SQL server高级语法

1. 公共表达式CTE

公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
如下面的例子，可以递归把组织名放到一起。

其实CTE的作用就相当于子查询

 

 

2.窗口函数、分区函数

窗口函数和聚集函数一样都是对定义的行集（组）进行聚集，但是不像聚集一样只返回一个值，窗口函数可以为每个组返回多个值，执行聚集的行组是窗口(因此称为‘窗口函数’)。窗口函数是在聚集函数的基础上加了一个 over()，所有的聚集函数都可以利用这种方式转换成窗口函数。窗口函数是最后才执行的，在order by 之前,where和group by之后

Partition By分区子句：可以根据partition by子句定义行的分区或组，以完成聚集，如果使用空括号，那么整个结果集就是分区，窗口函数将对它进行聚集计算，可以把Partition By看成是移动的Group By，可以用Partition By对定义的行组计算聚集(当遇到新的组时复位)，并返回每个值(每个组中的成员)，而不是用一个组表示表中这个值的所有实例。

 

 

窗口函数除了用于聚集函数sum,count,avg等之外，还有row_number(计算行数)，rank（排名）,lead() ,lag()前移后移 在日常工作中使用也很大；

 

3.FOR XML Path

这个在sql server中的作用主要是把行数据转列。在mysql中有group_concat，DB2中有listagg，而sql server中没有，所以用for xml path

如下，我要取得年月，直接查询是这样的

 

 

当我在后面加上了for xml path 后就得到了一行的结果：

 

用字符串处理函数去掉前面的第一个逗号，就可以得到一个可用的字符串，用于存储过程之类的；

 

4.PIVOT 和UNPIVOT 行列转换函数

PIVOT：行转列，下面的代码实现的是，选择orderid为71774和71780的两个产品作为列名，以productID作为行，得到汇总数据

 

 

UNPIVOT 列转行 贴一个官方教程的例子：

--Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

   FROM pvt) p

UNPIVOT

   (Orders FOR Employee IN

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt;

GO

 

运行结果：

 

 

5.Merge 的应用  主要用于更新数据,贴一个我写的存储

--Create the table and insert values as portrayed in the previous example.

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,

    Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO pvt VALUES (1,4,3,5,4,4);

INSERT INTO pvt VALUES (2,4,1,5,5,5);

INSERT INTO pvt VALUES (3,4,3,5,4,4);

INSERT INTO pvt VALUES (4,4,2,5,5,4);

INSERT INTO pvt VALUES (5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

   FROM pvt) p

UNPIVOT

   (Orders FOR Employee IN

      (Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt;

GO

 

6.动态sql

 文本拼接语句  缺点：1.容易被注入，被黑 最好不用  2.容易报错，如西安的拼音 xi'an

-- 实例1

DECLARE @sql NVARCHAR(1000)

SET @sql='select * from '+'[SalesLT].[Customer]'

PRINT @sql

--EXEC(@sql)

EXECUTE(@sql)

-- 实例2

DECLARE @sql NVARCHAR(1000),@i NVARCHAR(50)  

--SET @i=100

SET @sql=N'select getdate()'

SET @sql=@sql+';select @i'

--EXECUTE(@sql)

DECLARE @we NVARCHAR(50)='xi''an'

EXEC sys.sp_executesql @sql,N'@i NVARCHAR(50)',@we             -- 变量必须是unixcode 字符传入

  -- 可以传入参数

-- 实例3 可计算

DECLARE @sql NVARCHAR(1000),@i INT,@j int

--SET @i=100

SET @sql=N'select getdate()'

SET @sql=@sql+';select @i+@j'

--EXECUTE(@sql)

SET @i=500

SET @j=1

EXEC sys.sp_executesql @sql,N'@i INT,@j int',@i,@j

 

 

7.ON条件 在使用left jion时，on和where条件的区别如下：
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

举个例子：

先创建t1,t2两个表

CREATE TABLE t1 (

a CHAR(1)

)

INSERT INTO t1 VALUES('A'),('B'),('C')

CREATE TABLE t2 (

a CHAR(1)

)

INSERT INTO t2 VALUES('B'),('C'),('D')

 

以下是限制left join 左边的表的结果，可以看到上面的才是我们想要的结果

以下是限制t2的结果，可以发现把条件放在on后面才是我们想要的结果

 

 

8.Except 和Intersect

比较两个查询的结果，返回非重复值。
EXCEPT 从左查询中返回右查询没有找到的所有非重复值。
INTERSECT 返回 INTERSECT 操作数左右两边的两个查询都返回的所有非重复值，即二者交集。

还是用刚刚的表，t1中是ABC,t2中是BCD

那么EXCEPT返回的是A

INTERSECT返回的是BC