SQLserver⾼级语法
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。