SQL Server_常用查询 | Jimmy那些事儿

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

SQL 逻辑流

Select 语法

Select语句的 From部分 将所有数据源 组装进一个结果集 ，然后由Select语句的剩余部分对结果集执行操作。

Where子句作用于From组装的记录集，根据条件筛选某些行。

聚合函数对数据集执行求和操作。

Group by 子句根据在该子句中指定的列将大量数据集分组成较小的数据集。

Having 对较小的 数据组 执行聚合函数。

Order by 子句确定结果子的排列顺序。默认为升序；

Select [Distinct] [Top(n)] *, columns, or expressions
  [From data source(s)]
    [Join data source
	  ON condition] (may include multiple joins)
  [Where conditions]
  [Group by columns]
  [Having conditions]
  [Order by columns];

查询语句的逻辑流

数据源(From) —— 条件(Where) —— 列/表达式 (col/exp) —— Order by — 谓词

From，查询 首先组装初始数据集 。

Where，筛选；筛选过程实际上是 选择符合标准的行的where子句 。

Group by，组合数据的子集 [若要分组， 先对数据排序 ，然后根据排序后的数据进行聚合]

聚合， Aggregations，选择性地对数据进行聚合；如求平均值，按列中的值对数据分组以及筛选组；

Having，筛选数据的子集

列表达式 ：处理Select列，并计算任何表达式 [ 这个时候才涉及到列 ]

Order by，排序

Over，窗口函数和排名函数通过与其他聚合函数一起提供结果的 单独排序的视图

Distinct，从结果集中删除任何重复的行

Top， 选定行后 ，执行计算， 并按所需的顺序排序

Insert,Update,Delete，最后一个逻辑步骤是将数据修改操作应用到查询结果。

Output，选择插入和删除的虚拟表，并返回给客户端

Union，堆叠或合并多个查询的结果

SQL编写标准

若需要多个表合成一个表，必须确定 【唯一标识符】或者说是联接的表，从联接表中去寻找联接的字段

最后合并的中都必须要有这个字段

若出现多个主条件，先将主条件的区分变量统一到一个表中

其他表与这个表进行联接

先把各个字段单独用Select写出来

再合并为一个Select语句

运算符介绍

优先级

Not > 算术运算符(+-) > 条件运算符(where) > And > Or

电脑中字符优先级：数字>字母

1a > a1 > a11 > aa1

注意事项与通用规则

方括号 [ ]

why ：表名或字段名如果引用了sql server中的关键字，数据库会不识别这到底是关键字还是表名（还是 字段名 ）时就必须要加；

查询语句的表中加上方括号[ ] ，目的是以声明其不是保留字 ；

如果表名不是关键字，不用加方括号

# 一个表名叫user，user在sqlserver中属于关键字，那么查询的时候必须要这样
select * from [user] ;
# 若表名user中没有user的列，则无需加方括号
select * from user ;

合并：字段+表格

# 字段合并 +
SELECT 机构+客户名称  FROM allzjb  where 结算日期>'2015-11-15'
SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName) FROM Person.Person
--剪裁姓氏，并将逗号、两个空格和 Person 中的 AdventureWorks2012 表列出的人员名字串联起来

使用 “ + ” 连接多个字段， 合并成一列

前后类型应兼容；

如果+连接数值类型，结果是数值之和

如果+连接字符串类型，结果是字符串的连接

# 表格合并 - insert into A1 select ... 
INSERT INTO table2 SELECT * FROM table1;  -- 把table1的数值插入到table2中
# 只插入某一列的值
INSERT INTO table2 (column_name) 
  SELECT column_name FROM table1 ;

1
2
3

# 合并为一个字符串 - concat()
Select Concat(Null, 'Patrick ', 1, ' LeBlacn')
--隐式地将所有值转换为字符串，将空值转为空字符串

Where条件

最佳实践 ：找到事物的最好办法就是查找，而不是先排除不是该事物的所有东西。 即where条件，声明肯定的限制条件优于否定的限制条件；

1	where col >= 10 ; 优于 where col !< 9

惊叹号! ，不是ANSI标准的SQL；

布尔逻辑运算的优先次序： NOT > AND > OR

若where子句中同时出现 and 和 or 条件， 一定要把整个or条件子句用括号括起来

Where name Like 'Chain%' or ProductID Between 320 And 324 
  And name Like '%s%'  
--1. 先执行And，即找出name中 带有 s 的名字；
--2. 再在其中寻找 name中有Chain 或者 ProduceID在[320,324]
------------------
Where (name Like 'Chain%' or ProductID Between 320 And 324 )
  And name Like '%s%'
--1. 先执行括号，即先找出name中有Chain 或者 ProduceID在[320,324]
--2. 再在其中找出name中 带有 s 的名字；

不能在Where 子句中使用聚合函数。此时应用 子查询 来进行限定

先进行联合，再进行where条件的执行

1
2
3

Select * From A 
  Left Join B On A.name = B.name   ----因为先进行联合，再进行where条件选择，若在表B中存在不满足where条件的观测值，最后不会被选中
  Where B.col = 'x'

Between and

# 使用带有日期时间值的 BETWEEN 
WHERE RateChangeDate BETWEEN '2001-12-12' AND '2002-01-05';   
# 特别注意，若涉及到最小单位为小时的情况，则必须考虑多加一天；
BETWEEN '2001-12-12' AND '2002-01-05';  
# 表示选择的范围为 2001-12-12 00:00:00 ~ 2002-01-05 00:00:00 ; 1月5日的 09:00:00是不包含在内的【即1月5日的不包含】
----上面的示例检索所在的行【datetime值】可以介于'20011212'和'20020105'(含） 之间;因为在查询中的日期值和datetime值存储在RateChangeDate而无需在日期的时间部分中指定了列。
-- 下面是结果集：
BusinessEntityID RateChangeDate
3 2001-12-12 00:00:00.000
4 2002-01-05 00:00:00.000
----未指定时间部分时，将默认使用 12:00 A.M。 
--请注意，若某行的时间部分晚于 2002-01-05 12:00 A.M.， 则由于它处于范围之外，因此此查询不返回该行。
Select Distinct 结算日期 
  From [exchange].[GFFCC_YTX].[v_jsmx]
  Where 结算日期 between '2017/6/12' and '2017/6/15'
  order by 结算日期
--返回的结果为
2017-06-12,2017-06-13,2017-06-14   --返回的日期不包括6月15日，因为该表达式表示为 小于等于 2017-06-15 00:00

ALL、SOME、ANY

ALL，相当于And；如果子查询可能返回一个空值，那么使用ALL会判断为fasle，使用时要小心；

一个if，一个命令的执行 ；并且 没有Then和End来终止if命令；

If condition 
Statement;
----------------------------------
If 1=0
Print 'Line one';
Print 'Line two';
--结果返回Line two；

if语句之后没有分号 ； if语句实际上是后面语句的提前；

使用Begin / End 有条件地执行多条语句

If condition 
  Begin;
	Multipie Line;
  End;  --每个都有分号

使用If Exists()作为基于存在性的条件

If Exists() 结构使用从SQL Select语句 返回的每一行作为条件。

因为If Exists() 结构会查找每一行，所以Select语句应当选择所有的列。一 旦一个单行满足了If Exists()，查询就会继续执行

If Exists
  (Select * From production.product Where quantity = 0)
  Begin;
	Print 'Relpenish Inventory';
  End;

使用If / Else 执行替换语句

可选的Else定义了if条件为False时的执行代码；Else可控制 下一个单个命令，后者Begin/End块

If condition
 	 Single line or Begin/End block of code;
  Else
	Single line or Begin/End block of code;

while,break,continue的使用

设置重复执行 SQL 语句或语句块的条件。 只要指定的条件为真 ，就重复执行语句。可以使用 BREAK 和 CONTINUE 关键字在 循环内部控制 WHILE 循环 中语句的执行。

Break ：导致 从最内层的WHILE循环中退出 。将 执行出现在END关键字后面的任何语句 ，END 关键字为循环结束标记。

如果嵌套了两个或多个 WHILE 循环，内层的 BREAK 将导致退出到下一个外层循环。 首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。

Continue ：使 WHILE 循环 重新开始 执行， 忽略 CONTINUE 关键字后的任何语句 。

while  当条件为ture时循环代码；
----
WHILE Boolean_expression 
{ sql_statement | statement_block }   --若有Select语句，必须用圆括号（） --若要定义语句块，请使用控制流关键字 BEGIN 和 END。 
[ BREAK ]  --导致从最内层的WHILE循环中退出。将执行出现在END关键字后面的任何语句，END 关键字为循环结束标记。  --如果嵌套了两个或多个 WHILE 循环，内层的 BREAK 将导致退出到下一个外层循环。首先运行内层循环结束之后的所有语句，然后下一个外层循环重新开始执行。 
{ sql_statement | statement_block } 
[ CONTINUE ] -- 使 WHILE 循环重新开始执行，忽略 CONTINUE 关键字后的任何语句。 
-----------------------------------------------------------------------------
A. 在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE 
如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。
USE pubs 
GO 
while (select avg(price) from titles) <$30 
begin
 update titles
  set price=price*2
  select max(price) from titles
  if(select max(price) from titles) >$50
break
  else
continue
end
print 'too much for the marker to bear' 
B. 在带有游标的过程中使用 WHILE 
declare @i int 
set @i=1 
while @i<30 
begin 
insert into test (userid) values(@i) 
set @i=@i+1 
end

创建与变更格式

视图

----创建 视图
Create View schemaname.viewname[(column)]
As
Select ...From ...;
----删除视图
Drop View name

变更格式

----变更列的类型
ALTER TABLE table_name
ALTER COLUMN column_name data_type
----添加新列
1 ALTER TABLE table_name
2 ADD column_name data_type NULL  -- 表示 允许为NUll
----删除某列
ALTER TABLE table_name
DROP COLUMN [COLUMN_NAME]  --drop column set4_time
----重命名列名   下面的示例将 TerritoryID 表中的 Sales.SalesTerritory 列重命名为 TerrID。
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';  
----重命名表
exec sp_rename '[原表名]','[新表名]'
----添加主键
Alter table [表名] add constraint [ 约束名] primary key( [列名])
----添加唯一约束
Alter table [表名] add constraint [ 约束名] unique([列名])
----添加表中某列的默认值
Alter table [表名] add constraint [约束名] default(默认值) for [列名]
----添加外键约束
Alter table [表名] add constraint [约束名]  foreign key(列名) referencese 另一表名(列名)
----删除约束
Alter table [表名] drop constraint [约束名]

插入、更新、删除、合并数据

创建数据

Create Table table.name(
col_name1 [Int] Identity(1,1) Not Null, 
col_name2 datetime,
Constraint PK_table.name_col_name1 Primary Key CLUSTERED (col_name1))
 --IDENTITY [ (seed , increment) ]  --在表中创建一个标识列。 --seed,第一行所用的值； increment,增量值

若要重新定义主键，则必须首先删除与现有主键之间的任何关系，然后才能创建新主键。此时，将显示一条消息警告您：作为该过程的一部分，将自动删除现有关系。

1 2	--创建新列 ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ;

插入数据

# 插入简单的值行
Insert [Into] schema.table [ (colums, ...) ]
  Values (value,...),
	(value,...);
# 在标识列中插入数据，要使用Set Identity_insert on / off关键字
Set Identity_insert dbo.address On
  Insert Into dbo.address (AddreddID, Addressl, city, state, county)
  Valuse(999, )
Set Identity_insert dbo.address Off
------------------------------------------------------------------------------------
# 从Select语句中插入结果集
Insert [Into] schema.table [ (colums,...)]
  Select columns From data [Where conditions];
--1. schema.table 必须存在
--2. into schema.table中的列必须与之后Select中的列相同
--3. 若schema.table中有5列，只选取其中3列插入，则其他2列的观测值对NULL
------------------------------------------------------------------------------------
# 从存储过程插入结果集
Insert [Into] schema.table [ (colums,...)]
  Execute storedprocedure;

# 在插入数据时创建表
Select colums 
  Into newtable From data [where conditions];

SQL中合并列，只要选择对应的列即可

Select a.*, b.* From a Full Join b

更新数据

# 更新单个表
Update schema.table
  Set column = expression,
	column = value 
  [From data]
  [Where conditions];
----------------------------------------------------------------------------------
# 执行全局搜索与替代
# replace(data, 'aaa', 'bbb')
Update address
  Set county = Replace(county, 'sun', 'dark')  -- 将county列中所有的sun替换为dark
  Where county Like '%shine' ;

删除数据

# delete
Delete [From] schema.table
  /*[From data] SQL Server 特有的T-SQL扩展，用来进行联接；正常情况下应在where子句中创建子查询*/
  [Where conditions];
# 删除所有行
Delete From schema.table
--不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
---------------------------------------------------------------------------------
# 删除表中所有行 - truncate
Truncate Table dbo.address
---------------------------------------------------------------------------------
# 删除整个表
Drop table schema.table

truncate 、delete与drop区别

truncate和不带where子句的delete、以及drop都会删除表内的数据drop、truncate都是DDL语句(数据定义语言)，执行后会自动提交。Delete是DML语句(数据库操作语言)

truncate 和 delete 只删除数据 不删除表的结构(定义) ； drop 语句将删除表的结构 被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。 truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动。drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。

速度：drop> truncate > delete

TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但TRUNCATE TABLE 比DELETE速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

对于由FOREIGN KEY 约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHER 子句的DELETE语句。由于 TRUNCATE TABLE不记录在日志中，所以它不能激活触发器。

TRUNCATE TABLE不能用于参与了索引视图的表。

判断是否存在后删除

----临时表
if Object_id(N'tempdb.dbo.#a4',N'U') Is Not Null
  Drop table #a5
----判断数据表是否存在,若存在则删除数据表
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'Table_Name') DROP TABLE Table_Name;

Meger语句。使用 MERGE 语句在一条语句中执行插入、更新或删除操作。

有条件地在目标表中插入或更新行。

如果目标表中存在相应行，则更新一个或多个列；否则，会将数据插入新行。

同步两个表。 根据 与源数据的差别在目标表中插入、更新或删除行。

Meger flightpassengers As f         -- MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图
  Using checkin as c                --USING 子句用于指定要与目标联接的数据源
	On c.lastname = f.lastname      --ON 子句用于指定决定目标与源的匹配位置的联接条件
	And c.firstname = f.firstname
	And c.flightcode = f.flightcode
	And c.flightdate = f.flightdate
  When Matched 
	Then Update Set f.seat = c.seat
  When Not Matched By Target        --当Soucre表与Target表（基准表）不匹配时，对Target表进行操作
	Then Insert (Firsrname, Lastname, Flightcode, flightdate, seat)
  When Not Matched By Source        --当Target表与Soucre表（基准表）不匹配时，对Target表进行操作【相同】
	Then Delete;
--WHEN 子句（WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE）基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定所要采取的操作。
--OUTPUT 子句针对插入、更新或删除的目标中的每一行返回一行。
--必须由分号进行终止
--必须是一对一匹配；一对多匹配是不允许的
--联接条件必须是确定性的，也就是可重复的

返回修改后的数据

Output子句可以访问 插入的和删除的虚拟表， 以及任何在From自引用的数据源来选择要返回的数据。

Output子句有一个较为高级的应用，可以把输出数据传输到外查询。

# 从插入返回数据
Insert Into personlist
  Output Inserted.*    -- Inserted.
  Valuse(7777, 'Jane', 'Doe');
# 从更新返回数据 -- 可同时返回更新前、更新后的数据
Update personlist
  Set firstname = 'Jane', Lastname = "Doe"
  Output Deleted.firstname oldfirstname, Deleted.lastname oldflastname,  --Deleted.column oldcolumn
         Inserted.firstname newfirstname, Inserted.lastname newlastname
  Where businessentityID = 7777
# 从删除返回数据
Delete From personlist
  Output Deleted.*
  Where ...
# 从合并返回数据
...
  Output deleted.column, deleted.column,
         $action,                             -- 显示数据库操作的行为（为Insert、Delete、Update)
		inserted.column, inserted.column ;
# 把数据库返回到表中
Declare @Deletedperson Table(
  businessentityID Int Not Null Primary Key,
  lastname Varchar(50) Not Null,
  firstname Varchar(50) Not Null
)
Delete dbo.personlist
  Output Deleted.colunm, Deleted.column
  Into @Deletedperson
  Where bussinessentityID = 2;

1. 查询：字符串

行数&字符数

# 返回观测值的行数 - count [非空值的个数]
SELECT COUNT(name) FROM my_contacts;
# 返回字符串的字符个数 - len
SELECT LEN('中国人') ;  # 返回:3
# 返回字符串的字节数 - datalength
SELECT DATALENGTH('中国人');  # 返回：6

去空格

1 2	# 去空格 - ltrim / rtrim select ltrim(rtrim(' "左右都没有空格" ')); # 左右去空格

# 生成空格 - space
select space(2);  # 生成2个空格
SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName) FROM Person.Person
# 剪裁姓氏，并将逗号、两个空格和 Person 中的 AdventureWorks2012 表列出的人员名字串联起来

取子串

# 取子串：特定位置 - substring
select substring(name,1,2);  # 返回na;
# 取子串：左/右 - left /right
select left(ltrim( name), 3);  # 返回nam;
-------结合Charindex 返回特定符号之前的值
Select left('text', charindex('%','text',1)-1)  -- 第二个为返回 % 出现的位置
Select left('text', len('text')-1 ) --若指定符号为最后一个时

返回特定位置

# 返回位置：起始位置 - charindex
select charindex('L', 'HELLO', 1);  # 返回:3; 1表示从第一个位置开始选取
--charindex(serach string, string ,starting position) ；第三个参数默认为1，可不写
# 返回位置：表达式中某模式第一次出现的起始位置 - patindex
patindex('%123%','abc123def');  # 返回4
--允许通配符的使用
--返回第一个数字的位置
Select patindex('%[0-9]%','字段名')
--返回第一个数字的位置，并进行截取
substring(合约,1,patindex('%[0-9]%',合约)-1),

取出一个字符串中最后一个特殊字符右边的字符

涉及翻转


                              reverse

和


                              charindex

DECLARE @aa VARCHAR(30)
SET @aa = '10*20*300'
-- 取最后一个*后的所有字符
SELECT RIGHT(@aa, CHARINDEX('*',REVERSE(@aa)) - 1) 
-- 取定长字符串，如*后的3个字符
SELECT SUBSTRING(@aa, LEN(@aa) - CHARINDEX('*',REVERSE(@aa)) + 2, 3)

替换

# 替换 - replace
select replace('abcdef','cde','xxx'); 返回 abxxxf
Update address
  Set country = replace(County, 'sun', 'dark') 
--colname列中每个单元格的aaa值替换为bbb值，并返回colname列
# 删除&替换 - stuff()
Select stuff('abcdef', 3, 2, '123')  -- 返回ab123ef；从第三个位置开始删除2个字符，并插入123
--stuff(string, insertion position, delete count, string inserted);

合并

1
2
3

# 合并为一个字符串 - concat()
Select Concat(Null, 'Patrick ', 1, ' LeBlacn')
--隐式地将所有值转换为字符串，将空值转为空字符串

格式转换

# 转换：大小写 - upper/lower
select upper('abc');  # 返回 ABC
# 转换：反转 - reverse
select reverse('abc');  # 返回'cba'
# 转换：字符形式 - char
select char(213);
# 转换：字符串形式 - str
select str(123.45, 6,1);  # 把数值转换成字符串格式
--返回123.5； 将123.45转为6个位置的字符串，数字的小数部分舍入为1为小数；
# 转换：ascii码 - ascii
select ascii(123) as '123'

格式转换 - Convert

CONVERT (data_type[(length)], expression [, style])

length： nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

style： 日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。

# 格式的转换、显示 - convert : 将第2个参数转换为第1个参数所指定的类型 / 用不同的格式显示日期/时间数据。
-- CONVERT (data_type[(length)], expression [, style]) ;
-- [,style] 日期格式样式
--------------------日期转换------------------------
SELECT CONVERT(DateTime, '2020-09-09');  # 返回: 2020-09-09 00:00:00.000
SELECT CONVERT(varchar(5), 92.89);  # 返回：92.89
SELECT CONVERT(varchar(11), GETDATE(), 121);  # 返回：2010-03-24
常用日期格式：
  - 23 ：日期格式 yy-mm-dd
  - 111：日期格式 yy/mm/dd
  - 120：日期格式 yyyy-mm-dd hh:mi:ss(24h)
  - 121：日期格式 yyyy-mm-dd hh:mi:ss.mmm(24h)
  - 105：日期格式 dd-mm-yy
  - 110：日期格式 mm-dd-yy
-------------------其他格式转换-------------------
Select Convert(int, '3')
Select COnvert()

Data_type : INT / DECIMAL(10,2) / CHAR() / VARCHAR() /

格式转换 - Cast

CAST ( expression AS data_type )

data_type 目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。

1	select CAST('123' as int) -- 123

字符串 - 正则表达式

regexp_like(x,pattern[,match_option]) ：查看x是否与pattern相匹配

可选的参数match_option字符串说明默认的匹配选项。match_option的取值如下:

‘c’ 说明在进行匹配时区分大小写（缺省值）；
‘i’ 说明在进行匹配时不区分大小写；
‘n’ (.)点号能表示所有单个字符,包括换行(俺还不知道什么地方有用到换行.只知道sql里面可以用chr(10)表示换行.
‘m’ 字符串存在换行的时候当作多行处理.这样$就可匹配每行的结尾.不然的话$只匹配字符串最后的位置.

--可以查找ename中以a开头以n结尾的行
select * from emp
	where regexp_like(ename,'^a[a-z]*n$');
--例如ename为arwen或arwin或anden.但Arwen不能被匹配.因为默认是区分大小写.如果是
select * from emp 
	where regexp_like(ename,'^a[a-z]*n$','i')--则可以查找ename为Arwen的行记录.

regexp_replace

regexp_substr

regexp_instr

2. 查询：数字相关

3. 查询：聚合函数

聚合是在对From和Where子句筛选后的数据集进行聚合计算；即运营逻辑是在其之后执行
聚合函数，它的对象是多个观测值，但只 返回一个值 ；若要出现多个观测值的聚合值，需要用到 Group by 函数
一旦查询包含了聚合函数，那么每一列必须参与到聚合函数中。

查询：日期的应用

DATEDIFF

返回两个日期的差值； datediff(datepart,startdate,endate)

SELECT DATEDIFF(dd, ‘02/05/2003’, ‘02/09/2005’) 返回：735

DATEPART

返回指定日期部分的整数（整数形式）

SELECT DATEPART(dd, ‘01/09/2003’) 返回：9

DATENAME

返回指定日期部分的字符串（字符串形式）；工作日(dw)、周(wk)、日(dd)、月(mm)

SELECT DATENAME(dw, ‘02/02/2009’) 返回: 星期一

Eomonth

返回针对指定开始日期的月份的最后一天

Select Eomonth(‘5/27/1998’)

返回指定日期“年”部分整数

SELECT YEAR(GETDATE()) 返回：当前年份整数

MONTH

返回指定日期“月”部分整数

SELECT MONTH(GETDATE()) 返回：当前月份整数

返回指定日期“日”部分整数

SELECT DAY(GETDATE()) 返回：当前日期整数

# 获取当前时间 - getdate
select getdate();
# 返回指定的时间 - dateadd
select dateadd(mm, -1, '2017-03-31');  ----返回 2017-02-31
	# datepart：yy/qq/mm/ww/dd/hh/mi/ss/ms ; 
	# num为正或为负；
--dateadd()仅接受提取日期部分
# 计算两个时间差 - datediff
select datediff(dd,'2016-06-01', '2017-01-31'); # yy/qq/mm/ww/dd/hh/mi/ss/ms
--datediff(date_type,startdate , enddate)
# 取出时间的某一部分 - datename/datepart --只能取出 年 或者 月 或者 日 
select datename(dd, '2017-01-31');  # datename 字符串形式
select datepart(dd, '2017-01-31');  # datepart 整数形式
Select Convert(varchar(),,120)  ---- 可以取出前面的所有部分
--返回上一个月 Select Convert(varchar(7), dateadd(mm,-1,getdate()),120)
# 返回星期几 - detename
Select datename(dw, '2017-01-31')
# 获取日期的年份/季度/月度/日期等 
select year(getdate());  quarter/month/day
# 查看轴
select datepart(week,getdate())
-- 按周分组
select datepart(week,convert(datetime,dateTimeCloumn,121)) '周',count(distinct users) '人数'
# 返回针对指定开始日期的月份的最后一天 - Eomonth
Select Eomonth('2016-02-32',1)  # 返回 2016-03-31
Select EOmonth(getdate(),-1)
--Eomonth(start_date, month_to_add)

时间戳就是一个从1970-01-01 08:00:00到时间的相隔的秒数

dateadd(s, string, format)：时间戳转日期格式

datediff(s,format,time)：普通时间转时间戳

-- 时间戳转日期格式
SELECT DATEADD(S,1160701488,'1970-01-01 08:00:00')  
--普通时间转换成时间戳
SELECT DATEDIFF(S,'1970-01-01 08:00:00', GETDATE())

注解：北京时间与GMT时间关系

1.GMT是中央时区,北京在东8区,相差8个小时

2.所以北京时间 = GMT时间 + 八小时

1 2	> select DATEADD(second,1268738429 + 8 * 60 * 60,'1970-01-01 00:00:00') >

1	unix_timestamp(time) 这个转换成时间戳，from_unixtime(unix_timestamp(time)) 这个转换成datetime

Datepart 设置周末为第一天

-----2014-01-05为周日
-- The default first date in a week is Sunday, the value is 7
SELECT @@DATEFIRST  
-- Default DATEFIRST is Sunday
SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 2
-- Change the DATEFIRST to 1, Monday will be the first day of week.
SET DATEFIRST 1 
SELECT @@DATEFIRST  -- 1
-- After change the DATEFIRST to Monday
SELECT DATENAME(WEEK,'2013-12-31') AS WeekName  -- 53
SELECT DATENAME(WEEK,'2014-01-01') AS WeekName  -- 1
SELECT DATENAME(WEEK,'2014-01-05') AS WeekName  -- 1

要注意的是 SET DATEFIRST 只在 当前执行中有效 ，也就说比如新开一个查询页面继续查询 SELECT @@DATEFIRST 则还是显示默认值 7。

参考：日期缩写参考

datediff / datename / datepart /dateadd

5. 查询：表格相关

限定行数 - TOP

# TOP n( 前n行 )
SELECT TOP 5 *  FROM  allzjb;  #　查询所有数据的中前5个
------------------------
TOP n PERCENT ( 按百分比取数据 )
SELECT Top 30 PERCENT * FROM allzjb ;

With Ties

允许最后的位置包含多行，但这多行是完全相同的

1	Select top (10) With Ties listprice From ...

随机行选择

使用Top(1) 返回单行，且用Newid()随机排序结果；每次将返回一个随机值

涉及到较大的表时，可用Tablesample( n Percent/Rows)选项

由于是随机选择，可通过Repeatable()来指定 [效果同R语言中的set.seed()]

1
2
3

Select top(1) Lastname From person.person Tablesample(10 Percent) -- 随机选择10%的
  Repeatable(1234)
  Order by Newid();  --随机排序结果集

分组&排序

分组

Group by 会根据某一列的值将数据集自动分成子集。

对 分组集 来说，汇总行是每个子集中的每个 唯一值 组成的行

数据集被分成子组之后，聚合函数在每一个子组上执行。

对于Group by子句，空值NULL被认为是相等的，并被分组到单个结果行

Group by不局限于对列分组，也可以对 表达式执行分组 （但该表达式必须与Select中的相同）

分组后筛选

HAVING

what ： 指定组或聚合的搜索条件 ；HAVING 通常在 GROUP BY 子句中使用，对Group by 之后的表单进行条件筛选；但针对的是所有group by 的字段，而非第一个

Select 交易账号,建平仓,[交易间隔(s)] From #b11  where [交易间隔(s)] < '60'
  group by 交易账号,建平仓,[交易间隔(s)]
  having count([交易间隔(s)]) >= 2  
 --指的是对满足所有group by字段的观测值进行计数统计；因为每个观测值都已经按照分组划分为不完全相同的观测值（每行中必定有一个不相等），所以结果为0

如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。

--HAVING 子句从 SalesOrderID 表中检索超过 SalesOrderDetail 的每个 $100000.00 的总计
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal  
  FROM Sales.SalesOrderDetail  
    GROUP BY SalesOrderID  
    HAVING SUM(LineTotal) > 100000.00  
    ORDER BY SalesOrderID ;

Select 指定的每一列都应该出现在Group By子句中，除非对这一列使用了聚合函数；

排序：Order by

可以使用 表达式 来指定顺序

可以使用 列别名 指定顺序

可以使用 列的顺序位置 来进行排序

# 按某个组分组并排序




    
group by name
order by name;
----------------------
GROUP BY name
ORDER BY first_name, last_name DESC, SUM(age);  # 默认为升序排序（ASC）

窗口函数 - Select Over()

聚合子句

Sum(cola) Over(partition by col1 order by col2)
--其他聚合函数：avg/max/min /count 
# 根据分组求和
Over(Partition by col1) --根据col1的分组对cola进行求和； [若同一组别有多个维度，则求和的值是相同的]
# 根据排名求累计和
Over(Order by col2)--根据col2的顺序对cola列累计求和； [若同一组有多个维度，求和的值是累加的]
# 根据分组后的排名，求累积和
Over(Partition by col1 order by col2) --根据Col1的分组进行排名得出Col2的次序，并按col2的次序对cola进行分组求和

1 2	# 移动平均 CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)),1) AS MovingAvg

在窗口内 分区：Partition by

执行逻辑：先对查询结果进行排序，之后通过Partition by的列进行分区；

窗口函数只能在Select 或者 Group by 子句中

排名 - rank子句

# 排名：无重复排名 - Row_number（组内连续的唯一的)
row_number() over (order by col2);  # 对所有进行排名
row_number() over (partition by col1 order by col2);  # 根据COL1分组，在分组内部根据 COL2排序
-------------------------------------------------
#　排名：有重复排名 - Rank （若出现字段值相同，序号一样，下一个跳过1位[排名是非连续的]）
rank() over([partion by col1] order by col2); 对所有进行排名
ProductID   Name                   LocationID   Quantity Rank  
494         Paint - Silver         3            49       1  
495         Paint - Blue           3            49       1  
493         Paint - Red            3            41       3 
-------------------------------------------------
# 排名：有重复排名 - Dense_rank （若出现字段值相同，序号一样，后一个不跳过[排名是连续的]）
dense_rank() over([partion by col1] order by col2)
ProductID   Name                               LocationID Quantity Rank  
494         Paint - Silver                     3          49       1  
495         Paint - Blue                       3          49       1  
493         Paint - Red                        3          41       2 
-------------------------------------------------
# 排名：对序号进行分组处理 - Ntile
ntile (4) over ([partion by col1] order by col2);  # (4)表示分为4组

ntile函数的分组依据（约定）：

首先系统会去检查能不能对所有满足条件的记录进行平均分组，若能则直接平均分配就完成分组了；若不能，则会先分出一个组，这个组分多少条记录呢？就是（总记录数/总组数）+1 条，之所以分配（总记录数/总组数）+1 条是因为当不能进行平均分组时，总记录数%总组数肯定是有余的，又因为分组约定1，所以先分出去的组需要+1条。

分完之后系统会继续去比较余下的记录数和未分配的组数能不能进行平均分配，若能，则平均分配余下的记录；若不能，则再分出去一组，这个组的记录数也是（总记录数/总组数）+1条。

举个例子，将53条记录分配成5组，53%5 = 3不能平均分配，则将余数3平均分配到前3组（余数 = 分配的组数），然后比较余下的 53-(11*3)=20 条记录能否平均分配给未分配的2组，能平均分配，则剩下的2组，每组各20/2=10 条记录，分配完成，分配结果为：11，11，11，10，10。

分页

# Offset & Fetch 必须结合使用
Select ... From
  Order by 
  Offset n rows  # 表示跳过n行
  Fetch Next 20 rows only  # 表示返回之后的20行

case when

最大的优点是可以与Select语句“内联”

若else缺失，表示其他值返回NULL

Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略；

所以，如果根据金额大小来判定等级，必须要最高的金额写在最前面；

 ## 两者区别
1. 输入表达式，只能用于等同性(=)检查
2. 布尔表达式，不局限于等同行(=)检查
--用输入表达式，将与每个where子句中的值比较
Case <input expression>  --只能用于等同性检查，而不进行其他比较
   when <when exp> then <result exp>
   [n...]
   [else <result exp>]
End
--给每个when子句提供一个布尔表达式，求值为TRUE或FALSE [布尔值是“真” True 或“假” False 中的一个]
 Case
   when <Boolean exp> then <result exp>
   [n...]
   [else <result exp>]
End

## 1.简单Case语句
# 在SELECT语句中，CASE 简单表达式只能用于等同性检查，而不进行其他比较
SELECT ProductNumber, Category =  
      CASE ProductLine     --表示如果 ProductLine = R 则返回 Road； Case之后的变量，为比较的变量
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
  FROM Production.Product ； 
--
Select top 10 SalesOrderID % 10 As 'OrderLastDigit',ProductID % 10 As 'ProductLastDigit',
"How Colse ?" =  CASE SalesOrderID % 10 
         WHEN  ProductID % 1  THEN 'Exact Match'   -- 可在When子句中引用第二个列来做判断
         WHEN  ProductID % 1-1 THEN 'Within 1'  
         WHEN  ProductID % 1+1 THEN 'Within 1'  
         ELSE 'More Than One Apart'  
      END  
FROM Sales.SalesOrderDetail ；
-- % 表示返回余数； 5%2 返回1
-- 变量名在Case之前使用双引号（“”）说明，该变量是新创建的变量;但不建议如此；
------------------------------------------------------
## 2. 搜索Case语句
-- 没有输入表达式（即Case关键字与第一个When之间的部分）
-- When表达式必须求值为一个 布尔值；（Case简单语句中，When的表达式可以为1、3、Price+1[含运算]）
Select top 10 SalesOrderID % 10 As 'OrderLastDigit',ProductID % 10 As 'ProductLastDigit',
"How Colse ?" =  CASE   					  -- 没有输入表达式
   WHEN (SalesOrderID % 10) < 3 THEN 'Ends With Less Than Threes' 
   WHEN ProductID =6 THEN 'ProductID is 6'     -- 可在When子句中引用第二个列来做判断
   WHEN ABS(SalesOrderID % 10 - ProductID) <= 1 THEN 'Within 1'  
   ELSE 'More Than One Apart'  
      END  
FROM Sales.SalesOrderDetail ；
# Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略
# 可在条件表达式中混合搭配的字段
# 可执行为任何表达式，只要最后的结果为布尔值

搭配其他函数用法

# SELECT CASE WHEN 用法
select userID ,  
count(CASE WHEN letterType='干部介绍信' then '1' end)干部介绍信数,  
count(CASE WHEN letterType='转递档案通知单' then '1' end)转递档案通知单数  
from T_LettersRecord GROUP BY userID  
------------------------------------------------------
# WHERE CASE WHEN 用法
SELECT l.letterType, u.realName  
   FROM T_LettersRecord as l, T_User as u  
  WHERE (CASE 
           WHEN l.letterType = '干部介绍信' AND u.userID = '1'  THEN 1  
           WHENl.letterType = '干部介绍信' AND  u.userID <> '1'  THEN 1  
         ELSE 0   
         END) = 1  
------------------------------------------------------
# 在 ORDER BY 子句中使用 CASE 
--计算 SalariedFlag 表中 HumanResources.Employee 列的值。SalariedFlag 设置为 1 的员工将按 BusinessEntityID 以降序顺序返回。 SalariedFlag 设置为 0 的员工将按 BusinessEntityID 以升序顺序返回
SELECT BusinessEntityID, SalariedFlag  
  FROM HumanResources.Employee  
  ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
------------------------------------------------------
# GROUP BY CASE WHEN 用法
SELECT   
CASE WHEN salary <= 3000 THEN 'T1'   
WHEN salary > 3000 AND salary <=8000  THEN'T2'   
WHEN salary > 8000 AND salary <=12000  THEN'T3'   
WHEN salary > 12000 AND salary <= 20000 THEN 'T4'   
ELSE NULL END 级别名称, -- 别名命名  
COUNT(*)   
FROM    t_userSalary  
GROUP BY   
CASE WHEN salary <= 3000 THEN 'T1'   
WHEN salary > 3000 AND salary <=8000  THEN'T2'   
WHEN salary > 8000 AND salary <=12000  THEN'T3'   
WHEN salary > 12000 AND salary <= 20000 THEN 'T4'   
ELSE NULL END;

联接与联合

what：将两个数据集相乘，并对结果进行限制。这样只返回两个数据集的交集。

why ：横向合并两个数据集，并通过匹配一个数据源的行与另一个数据源的行，从组合中产生新的数据集。

内联接

Inner Join
在连接条件中使用等于号（=）运算符，其查询结果中列出被连接表中的所有列， 包括其中的重复列 。
在连接条件中使用除等于号之外运算符（>、<、<>、>=、<=、!>和!<）
多个数据源的联接（顺序并不重要）
所要获得字段的表要写在最前面；即From之后；

可实现A联接B，B联接C，C联接D；

Select cst.companyname, prod.name 
  From Customre As a
	Inner join salesorderhead As b On a.customerid = b.customerid
	Inner join salesorderdetail As c On b.salesorderid = c.salesorderid
	Inner join product As d On c.productid = d.productid

SQL内在处理逻辑：(按照排列顺序进行联接)

Inner Join 表a 和表b

然后将 a和b 联合表单与表c Inner Join

最后将上面联合表与表d Inner Join

可 多条件连接 ；即在原来连接的基础上再进行连接

>   From #a2 As a
>   Left Join [exchange].[GFFCC_YTX].[v_qhkh] As b
>     On a.手机 = b.手机
>   Left Join [ADHOC_YTX].[zhangcm].[期货客户属性表] As c
>     On b.资金号 = c.投资者代码
>

what：以一个表为基准表，进行联接；

若使用外联接， 顺序非常重要

慎用不等连接


                                            on a.id <> b.id

，因为会对每一条记录a无法匹配的b表中的行生成一条记录，最后会生成 $a \times b$ 行

select * form tab1 left join tab2 on (tab1.size != tab2.size)
tab1.id	tab1.size	tab2.size	tab2.name
1		10			20			BBB
2		10			20			CCC
2		10			(null)		(null)
3		20			10           AAAA
......

how ：无论是否匹配，外联接都包含所有数据

How ：数据库在通过连接两张或多张表来返回记录时，都会生成一张 中间的临时表，然后再将这张临时表返回给用户 。

中间表 是左表与右表的 所有字段

on 与 where 的区别

on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录

select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

表示 同时满足 这两个条件 才能匹配 到记录 （必须用括号 () 括起来） ，但条件不为真也会返回左表中的记录；

可通过where子句限定 b.x Is Not Null 来返回该条记录或 where tb2.name=’AAA’

where条件是在临时表生成好后，再对临时表进行过滤的条件。条件不为真的就全部过滤掉。

关于 “A LEFT JOIN B ON 条件表达式” 的一点提醒

ON 条件（ “A LEFT JOIN B ON 条件表达式” 中的 ON ）用来决定如何从 B 表中检索数据行。

如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据

在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后， WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’
/*
1.中间表：on条件:  tab1.size = tab2.size	
tab1.id	tab1.size	tab2.size	tab2.name
1		10			10			AAA
2		20			20			BBB
2		20			20			CCC
3		30			(null)		(null)
 
2.再对中间表过滤：where 条件：tab2.name=’AAA’	
tab1.id	tab1.size	tab2.size	tab2.name
1		10			10			AAA
*/
select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)  --表示同时满足这两个条件才能匹配到记录(条件不为真也会返回左表中的记录)	
/*
1. 中间表 ： on条件: tab1.size = tab2.size and tab2.name=’AAA’
tab1.id	tab1.size	tab2.size	tab2.name
1		10			10			AAA         ----仅同时满足的进行匹配，但仍然会返回所有的观测行；
2		20			(null)		(null)
3		30			(null)		(null)
 */

其实以上结果的关键原因就是 left join,right join,full join 的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。

当涉及多个联接时

一定要用较小规模的数据对查询进行单元测试；

并坚持使用左外联接

外联接中的条件设置（执行逻辑）

当条件位于Join子句中，先包括外表的所有行，然后用条件包括第二个表中的行

当条件位于Where子句中，先执行联接，然后将条件应用于联接行

 # 条件位于Join子句
  Select a.col, b.col 
    From table As a
    Left join table2 As b 
  	On a.id = b.id 
  	And a.Lastname = 'Adams'  --该条件限制的是第二个表中的行
  	[And a.Lastname = b.name]  --也可以再进行限制
------------------------------------------
# 条件位于Where子句中
Select a.col, b.col 
  From table As a
  	Left join table2 As b 
  	   On a.id = b.id 
  Where a.Lastname = 'Adams'   --该where条件是对联接后的表进行限制

Left [Outer] Join

返回左表中的所有行，如果左表中行在右表中没有匹配行，则结果中右表中的列 返回空值 。

必须要表基准表放在左侧，非常重要！否则会产生大量NULL值。

Right [Outer] Join

恰与左连接相反，返回右表中的所有行，如果右表中行在左表中没有匹配行，则结果中左表中的列 返回空值 。

建议左/右外联接不要混合使用

Full [Outer] Join

返回 左表和右表中的所有行 。即返回两个数据集的所有数据。当某行在另一表中没有匹配行，则另一表中的列返回空值

交叉联接

Cross Join

不带WHERE条件子句，它将会返回被连接的 两个表的笛卡尔积 ，返回结果的 行数等于两个表行数的乘积

（例如：T_student和T_class，返回4*4=16条记录）

如果带where，返回或显示的是匹配的行数。

Left Join / Null

what：分析两个数据集之间相关性的查询；

why ：用于查找不存在或不匹配的数据

how ：一般需分两步来执行

设置第二个数据集的主键为NULL

涉及where子句， or的条件子句 一定要用括号 () 括起来

-- 左差集查询
--不在右表中的左表数据集；【可用Inner Join 中不等号为条件进行联接 <>】
Select c.customerid, so.ordernumber 
  From customer As a
  	Left join salesorder As b On a.customerid = b.customerid
  Where b.ordernumer Is Null;  --设置第二个表的主键为NULL
-- 返回在右表中的左表的值
where  so.ordernumer Is Not Null;  --设置第二个表的主键为不为NULL，即与之
# 全差集查询
Select c.customerid, so.ordernumber 
  From customer As c
 	Left join salesorder As so On c.customerid = so.customerid
  Where so.customerid Is Null 
	or c.customerid Is Null  
--设置两个表的的主键为NULL

Not In Like ( ) ：小范围使用

SELECT name FROM my_contacts
  WHERE name [NOT] IN LIKE ("A%","B%","C%")
----联合
SELECT name FROM my_contacts
  WHERE name NOT IN (Select name From B)
----子查询  查询选修了c02号课程的学生的姓名和所在系，学生表(student)，课程表(sc)
SELECT Sname, Sdept FROM student
  WHERE Sno IN (SELECT Sno FROM sc WHERE Cno = 'C02');  # 可在IN之前加NOT；即 WHERE sno NOT IN ...

Exists & Not Exists ：两个表之间；

Exists ：返回仅在左表中的数据

Not Exists ：外层查询结果不存在于关联表里的方法（ 返回左边不在右表里面的观测值 ）

注意 Where 之后 没有限定列名 , 条件限定在子查询中

---- 常用于外层查询结果不存在于关联表里的方法
--注意 Where 之后没有限定列名,条件限定在 子查询中
SELECT TNAME,DEPART FROM teacher   
  WHERE NOT EXISTS (SELECT * FROM course WHERE teacher.TNO = course.TNO);

EXCEPT ：返回在坐标中存在，但不在右表中的行；

从左查询中返回右查询没有找到的所有非重复值。

SELECT ProductID 
	FROM Production.Product
EXCEPT
SELECT ProductID 
	FROM Production.WorkOrder ;

联合 - Union

返回每个结果集中的所有行

Union ，表示删除重复行

Union ALL ，表示 不考虑是否存在重复行

每个Select必须具有 相同的数量、类型 ；

列名或别名 由第一个Select确定

Order by子句放在最后 ，并且对所有结果进行排序，且列名必须是第一个Select语句中存在的

可用Select Into，但 Into必须放在第一个Select语句中

列的xml数据类型必须为等效。

所有的列必须类型化为 XML 架构或是非类型化的。如果要类型化，这些列必须类型化为相同的 XML 架构集合。

子查询

非相关子查询

what：子查询是独立运作的；

how ：运行逻辑

非相关子查询被执行一次

结果传输到外查询

外查询被执行一次

1	Select (Select 3) As subqueryvalue;

公用表表达式- CTE

what：CTE (Comman Table Expression )

why ：确定查询结果可当做临时视图来使用；即后续的查询可以引用公共表表达式中的表及字段；

how ：CTE使用With子句，而With子句定义了CTE；在With子句内分别是名称、别名、AS、括号、Select查询语句

CTE自身只是 一个不完整的SQL语句

一旦With子句中定义了CTE， 查询的主要部分就可以使用其名称引用CTE ；就像CTE是其他任何表源一样；

若将 多个CTE包含 在相同的查询中，在主查询之前确定CTE的顺序，并用 逗号分隔

；并且后面的CTE可以引用在它之前定义的任何CTE

CTE不能嵌套

# With子句的表达式格式
With CTEname [Col Aliases]
  As (Select ...From ...
  )
, CTEname2 [Col] As (Select)  ----多表共同表达式
Select ...From ...
------------------------------------------------------
With 
  CTEname1 (col names) As (Select) ,  ----用逗号分隔
  Ctename2 (col names) As (Select)
Select ...
  From CTEname1
	Inner join CTEname2 On

数据透视 - Pivot

--------------------
SELECT <非透视的列>,   ---------第一个Select 表示从 From 中的数据中返回的值
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM 
    (<生成数据的 SELECT 查询>)   ---------第二个Select 表示从 Pivot所要用到的数据
    AS <源查询的别名>   -----As 的别名不可以省略
PIVOT
(
    <聚合函数>(<要聚合的列>)   -----要计算的列
FOR
[<包含要成为列标题的值的列>]   ---------观测行所在的列名
    IN ( [第一个透视的列], [第二个透视的列],   --------------上列中观测行的名称；可以理解为分类
    ... [最后一个透视的列])
) AS <透视表的别名>   -----As 的别名不可以省略
<可选的 ORDER BY 子句>;

--------原始数据
结算日期 多空罗盘	期初权益			盈亏	佣金			损佣比		佣金转化率
2017-08-25	0	32889.532389	-99.282000	120.902355	-0.821175	0.003676
2017-07-28	1	12964.049298	-80.327000	45.525612	-1.764435	0.003511
2017-09-01	1	24381.504055	-109.686000	70.774820	-1.549788	0.002902
2017-08-01	0	36143.075381	-410.239000	55.022701	-7.455813	0.001522
2017-08-30	0	33944.379618	-35.971500	94.818393	-0.379372	0.002793
2017-07-19	1	9448.754481		22.619500	25.076349	0.902025	0.002653
2017-08-29	0	33323.606632	-55.551000	129.617184	-0.428577	0.003889
2017-08-21	1	17932.778420	-234.807000	83.943086	-2.797216	0.004680
--------Pivot转换
Select 结算日期 
        , [1] As 期初权益_开通产品
        , [0] As 期初权益_未开通
    From (Select 结算日期,多空罗盘,期初权益
            From [ADHOC_YTX].[caihf].[多空罗盘_交易数据]) As a 
        Pivot (Max(期初权益) For 多空罗盘 In([1],[0]))  As b
------------多个列合并
Select b1.*, b2.*
    From (Select 结算日期,多空罗盘,期初权益
            From [ADHOC_YTX].[caihf].[多空罗盘_交易数据]) As a1 
        Pivot (Max(期初权益) For 多空罗盘 In([1],[0]))  As b1, ----------用逗号分隔符即可
	(Select 结算日期,多空罗盘,期初权益
            From [ADHOC_YTX].[caihf].[多空罗盘_交易数据]) As a2 
        Pivot (Max(期初权益) For 多空罗盘 In([1],[0]))  As b2)

----------未加工的数据
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost 
FROM Production.Product
GROUP BY DaysToManufacture;
DaysToManufacture          AverageCost
0                          5.0885
1                          223.88
2                          359.1082
4                          949.4105
----------Pivot转化后的数据
-- Pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Cost_Sorted_By_Production_Days    0         1         2           3       4       
AverageCost                       5.0885    223.88    359.1082    NULL    949.4105

unpivot

UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 会执行一次聚合，从而将多个可能的行合并为输出中的单个行。而 UNPIVOT 不会重现原始表值表达式的结果，因为行已经被合并了。另外，UNPIVOT 的输入中的空值不会显示在输出中，而在执行 PIVOT 操作之前，输入中可能有原始的空值。

数据清洗

去重复值
1
2
--Distinct # 去除完全重复的值




    
SELECT DISTINCT 机构 FROM allzjb ;
1
2
3
4
5
6
7

--Row_number() over(partation by col1 order by col2) 某个字段有重复值
--【先根据某相同字段分组，根据其他字段排序，创建临时表；在提取排序=1的信息】
# 对于相同的交易账号，取激活时间最前面的那一个账户的相关信息；
Select *, Row_number() Over(partition by 交易账号 order by 激活时间) as 排序 Into #11
From exchange.ytx.ext_激活客户归属表;
Select * Into #2 From #11 where 排序 = '1';

what：空值NULL表示 不存在的值，是一个未知值 ；并不表示0；
how ：包含 空值的任何表达式结果均是一个未知值
Null + 1 = Null

Count( * ) 会计算空值；但Count( col )会排除空值

1
2
3
4
5
6

# 测试空值 - IS NULL
SELECT * FROM allzjb where 机构 is null ;
-------------------------------------------------------
# ISNULL 将NULL替换为某个值
SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; # 将Weight中的NULL替换为50

isnull(col, 0 )：将col列中NULL值替换为0

1
2
3
4
5
6
7
8
9
10
11
12
13

## 处理空值
# Isnull() --将NULL替换为某个值
Select Isnull(col, 0) -- 对col列进行搜索，并将空值NULL转换为0；也可以是其他任意值/字符串
--isnull(soucre_expression, prlacement_value) # isnull是T-SQL特有的函数
-----------------------------------------------------------------------------
# Coalesce()
Select Coalesce(Null, Null+1, 1+2, "abc") 返回3
--Coalesce(expression, expression,... ) # 接受一系列表达式或列，返回第一个非空值
-----------------------------------------------------------------------------
# Nullif()

NULL ：未定义的值 / 不存在
NA ：缺失数据
NaN ：无意义的数，比如sqrt(-2)， 0/0。
Inf ：正无穷大
-Inf ：负无穷大

 表是否存在
1
2
3
4
5
6
7
8
9
10
11
12
# SQL SERVER中查询某个表或某个索引是否存在
IF OBJECT_ID(N'表名称', N'U') IS NOT NULL 
DROP TABLE 表名称;
--注意，普通表和临时表的使用差别：
--若希望删除TEST库中的dbo.TestTable表，直接将dbo.TestTable作为表名即可。
--若希望删除临时表dbo.#temp_table，需要以tempdb.dbo.#temp_table作为表名。
# 查询表上的某个索引是否存在
SELECT 1 FROM sys.indexes WHERE object_id=OBJECT_ID(@tname, N'U') and NAME=@iname
其中：@tname表示建索引的表名，@iname表示索引名。
在每一个数据库中都有sys.sysobjects用于包括在数据库中创建的每个对象（例如约束、默认值、日志、规则以及存储过程）。详细的说明信息参看MSDN上的帮助文档：sys.sysobjects

OBJECT_ID 的作用是 返回架构范围内对象的数据库对象标识号 。如果找不到数据库或对象的名称，例如相应名称不存在或拼写不正确，则会返回NULL。

sys.indexes 用于保存每 个表格对象 （例如，表、视图或表值函数）的 索引或堆 ，详细的说明信息参看MSDN上的帮助文档：sys.indexes

数据运算与其他

两行观测值相减
1
2
3
4
5
6
7
--根据某个字段先排序进行编号，创建为#a1；复制为另一个表#a2；通过联合，使得联合的条件为 #a1.rank+1 = #a2.rank
Select *, order by 成交时间 as rank   --报错
	into #a1 From [exchange].[YTX].[v_allcj] as cj;
Select * into #a2 From #a1;
Select a1.*, datediff(ss,#a1.成交时间,#a2.成交时间) s [交易间隔(s)] From #a1 
  Left join #a2 On #a1.rank = #a2.rank +1
1
2
3
4
5
6
7
8
9
10
11
12
13

----根据某个字段【分组】后排序编号；再通过唯一列联接，再设置条件 a.rank+1=b.rank 进行相减【理论上应该是 a.rank=b.rank+1】
--若a.rank=b.rank+1, 表示a表的rank=1 与b表的rank=2比较；
--若a.rank+1=b.rank,表示a表的rank=1 变更为rank=2 与表b进行比较
Select *, Row_number() over(partition by 交易账号 order by 成交时间) as [rank] into #a1
From [exchange].[YTX].[v_allcj] as cj;
Select * into #a2 From #a1;
Select #a1.*, datediff(ss,#a1.成交时间,#a2.成交时间) as [交易间隔(s)] From #a1
Left join #a2 On #a1.交易账号 = #a2.交易账号
where #a1.rank+1 = #a2.rank
and datediff(ss,#a1.成交时间,#a2.成交时间) < '60'

已知交易日计算差值
1
2
3
4
5
6
# 已知交易日，计算未交易的交易日间隔；
Select Max(结算日期) As 最后交易日期 From
Select Count(1) From       -----------计数
	Inner Join b On 最后交易日 < 结算日期
判断表单存在并删除
1
2
If Object_id(N'tempdb.dbo.#b1',N'U') Is Not Null Drop table #b1
--若非临时表，无需加 数据库名
计算累加和
1
2
Select Sum([求和列]) Over(Partition by [分组列] Order by [排序列])
	From ...
选取重复值
通过 group by 之后 having count(*)>1 来进行筛选

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

--1.查某一列(或多列)的重复值(只可以查出重复记录的值
---查找stuid，stuname重复的记录
Select stuid，stuname
from stuinfo
group by stuid，stuname
having(count(*))>1
--2.查某一列有重复值的记录 (所有重复的记录，如果有两条记录重复的，就查出两条))
--查找stuid重复的记录
select * from stuinfo
where stuid in (select stuid from stuinfo group by stuid having(count(*))>1 )
/*--3.查某一列有重复值的记录(只显示多余的记录，也就是说如果有三条记录重复的，就显示两条) —— 实际意义？？
--查找stuid重复的记录; 前提：需有一个不重复的列，此示例为recno。
select * from stuinfo s1
where recno not in (select max(recno) from stuinfo s2 where s1.stuid=s2.stuid)
*/

取出一个字符串中最后一个特殊字符右边的字符
涉及翻转 reverse 和 charindex

1
2
3
4
5
6
7
8

DECLARE @aa VARCHAR(30)
SET @aa = '10*20*300'
-- 取最后一个*后的所有字符
SELECT RIGHT(@aa, CHARINDEX('*',REVERSE(@aa)) - 1)
-- 取定长字符串，如*后的3个字符
SELECT SUBSTRING(@aa, LEN(@aa) - CHARINDEX('*',REVERSE(@aa)) + 2, 3)

问题 - 函数的长度参数无效
用逗号的位置得到的参数-1后是负数，不能做 LEFT和SUBSTRING的参数。
通过排序来进行查看，并且用Where进行限定

1
2
3
4
5

Select *, Convert(varchar(10), createTime, 120) As createTime2
, left(department, charindex('后', department,1)-1) As 老师
From [crm].[YTX].[t_wx_contact_monitor]
where tag = 'v11.JY.JY-QH-WECHAT-QY.0' and error_code= 0
and charindex('后', department,1)-1 >0

问题 - 遇到 “遇到以零作除数错误”
屏蔽了“遇到以零作除数错误” 的错误信息，这样的话，遇到0是除数的情况，就是赋值为Null

1
2
3
4
5
6
7
8
9
10

SET ANSI_WARNINGS OFF;
SET ARITHIGNORE ON;
SET ARITHABORT OFF;
GO
SELECT 1 / 0
SELECT Total/CountNr FROM dbo.TmpA1
-----------
NULL

系统查询相关

通过字段找表名
1
2
3
4
------
SELECT sb.name 
FROM syscolumns s JOIN sysobjects sb ON s.id=sb.id
WHERE  s.name='你的字段名'
-- 双连字符；从开始到行尾均为注释； 对于多行注释，必须在每一个开头多使用
/*……*/ 正斜杠星号；
可在同一行，可另起一行； 在这之间的全部内容均为注释（可跨行）；

可在 代码内执行

1
2
3
4

select * from #100
pivot(
sum(人数) for 资金等级 in ([100万以下])) as B 转置



1. SQL 逻辑流

1.1. Select 语法

1.2. 查询语句的逻辑流

1.3. SQL编写标准

 2. 运算符介绍

2.1. 优先级

2.2. 通配符

3. 注意事项与通用规则

3.1. 方括号 [ ]

3.2. 合并：字段+表格

3.3. Where条件

3.4. Between and

3.5. ALL、SOME、ANY

3.6. 循环

3.6.1. while,break,continue的使用

4. 创建与变更格式

4.1. 视图

4.2. 变更格式

5. 插入、更新、删除、合并数据

5.1. 创建数据

5.2. 插入数据

5.3. 更新数据

5.4. 删除数据

5.4.1. 判断是否存在后删除

5.5. 合并数据

5.6. 返回修改后的数据

6. 1. 查询：字符串

6.0.1. 行数&字符数

6.0.2. 去空格

6.0.3. 取子串

6.0.4. 返回特定位置

6.0.4.1. 取出一个字符串中最后一个特殊字符右边的字符

6.0.5. 替换

6.0.6. 合并

6.0.7. 格式转换

6.0.8. 格式转换 - Convert

6.0.9. 格式转换 - Cast

7. 字符串 - 正则表达式

8. 2. 查询：数字相关

9. 3. 查询：聚合函数

10. 4. 查询：日期相关

10.1. 查询：日期的应用

10.2. 时间戳

10.3. Datepart 设置周末为第一天

10.4. 参考：日期缩写参考

11. 5. 查询：表格相关

11.1. 限定行数 - TOP

11.1.1. With Ties

11.1.2. 随机行选择

11.2. 分组&排序

11.2.1. 分组

11.2.2. 分组后筛选

11.2.3. 排序

12. 窗口函数 - Select Over()

12.1. 聚合子句

12.2. 排名 - rank子句

12.3. 分页

13. 条件查询

14. 联接与联合

14.1. 内联接

14.2. 外联接

14.3. 交叉联接

14.4. 差集查询

14.5. 联合 - Union

15. 子查询

15.1. 非相关子查询

15.2. 公用表表达式- CTE

15.3. 相关子查询

16. 数据透视 - Pivot

16.1. unpivot

17. 数据清洗

17.1. 去重复值

17.2. NULL

17.3. 表是否存在

18. 数据运算与其他

18.1. 两行观测值相减

18.2. 已知交易日计算差值

18.3. 判断表单存在并删除

18.4. 计算累加和

18.5. 选取重复值

18.6. 取出一个字符串中最后一个特殊字符右边的字符

18.7. 问题 - 函数的长度参数无效

18.8. 问题 - 遇到 “遇到以零作除数错误”

19. 系统查询相关

19.1. 通过字段找表名

20. 注释