获取处理订单数超过100的订单年度及其客户数量：SELECT * FROM (SELECT orderyear, COUNT(DISTINCT custid)) AS numcusts

FROM (SELECT YEAR(orderdate) AS orderyear, custid FROM sales.[order]) AS D1 GROUP BY orderyear) AS D2 WHERE numcusts > 100

嵌套的CTE

WITH D1 AS ( SELECT YEAR(orderdate) AS orderyear, custid FROM sales.[order] GROUP BY orderyear ), D2 AS( SELECT orderyear, COUNT(DISTINCT custid)) AS numcusts FROM D1 ) SELECT * FROM D2 WHERE numcusts > 70

递归的CTE

这个比较有意思，比如想在员工表中获取当前雇员的最大BOSS时很有效哦

WITH empsCTE AS(

SELECT * FROM hr.employee WHERE empid = 6 --定位点元素

UNION ALL

SELECT c.* FROM empsCTE AS p JOIN hr.employee AS c ON c.empid = p.manageid --递归元素

)

SELECT * FROM empsCTE WHERE manageid IS NULL

视图

IF OBJECT_ID('sale.ChinaCusts') IS NOT NULL

DROP VIEW sale.ChinaCusts

GO

CREATE VIEW sale.ChinaCusts AS

SELECT * FROM sale.Customer WHERE country = 'China'

内嵌表值函数

IF OBJECT_ID('dbo.GetOrderByUID') IS NOT NULL

DROP FUNCTION dbo.GetOrderByUID

GO

CREATE FUNCTION dbo.GetOrderByUID

(@uid AS INT) RETURNS TABLE

AS

RETURN

SELECT * FROM sales.[order] WHERE uid = @uid;

GO

SELECT * FROM dbo.GetOrderByUID(8888) AS O;

返回每个客户3个最近的订单：

SELECT c.custid, a.orderid, a.orderdate

FROM sales.customer as c CROSS[OUTER] APPLY

(SELECT TOP(3) orderid, empid, orderdate, requiredate FROM sales.[order] AS o WHERE o.custid = c.custid

ORDER BY orderdate DESC, orderid DESC) AS a

元数据查询类型

解释与示例

UNION [ALL], INTERSECT, EXCEPT

SELECT country, region, city FROM address UNION SELECT country, region, city FROM user order by country

复杂情况

对前置查询进行复杂操作，获取1、6号员工最近的2个订单,使用表表达式：

SELECT empid, orderid, orderdate FROM (SELECT TOP 2 empid, orderid, orderdate

FROM [order] WHERE empid = 1 ORDER BY orderdate DESC) AS O1

UNION ALL

SELECT empid, orderid, orderdate FROM (SELECT TOP 2 empid, orderid, orderdate

FROM [order] WHERE empid = 6 ORDER BY orderdate DESC) AS O2

INTERSECT[EXCEPT] ALL的替代方案

实际SQL SERVER还不支持这种类型的操作，理解起来有点复杂，简单来说就是如果我的子查询A, B都有重复数据，一个是3条，一个是5条， 那么其INTERSECT ALL操作结果应该为3条，EXCEPT ALL的结果是2条。代码如下，重点是熟悉开窗函数的使用。

SELECT row_number() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country, region, city FROM address

INTERSECT

SELECT row_number() OVER(PARTITION BY country, region, city ORDER BY (SELECT 0)) AS rownum, country, region, city FROM user

这儿注意的是ORDER BY (SELECT 0)的用法，表示告诉系统不用排序的意思，减少不必要的开销。

开窗函数类型

解释与示例

排名开窗函数

其中包含4种类型的排名函数，ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()，最常用的是ROW_NUMBER,介绍一个分页场景 WITH CTE AS( SELECT ROW_NUMBER() OVER(ORDER BY custid) AS rownum, * FROM Sales.Customers) SELECT * FROM CTE WHERE rownum > 10 AND rownum <= 20 接下来介绍一个分区内排序，要求选取每个雇员最大的3单金额及其排名 WITH CTE AS( SELECT ROW_NUMBER() OVER(PARTITION BY empid ORDER BY freight DESC) AS rownum_ingroup, * FROM Sales.Orders) SELECT empid, freight, rownum_ingroup FROM CTE WHERE rownum_ingroup >= 1 AND rownum_ingroup <= 3

偏移开窗函数

涉及LAG、LEAD、FIRST_VALUE、LAST_VALUE四个函数，这儿就介绍LEG和LEAD，表示当前记录的前一个记录和后一个记录，记得在上篇的子查询有写过一种"小于该值的最大值"的方式，这儿使用函数更加的简单。 SELECT orderid, freight, LAG(freight) OVER(ORDER BY orderid) AS pre_freight, LEAD(freight) OVER(ORDER BY orderid) AS next_freight FROM Sales.Orders 这儿比较奇葩的是LAG用于获取前一条记录，LEAD获取后一条记录，不得不说设计的小伙伴那天"脑袋不小心被门夹了下"，哈哈

聚合开窗函数

看到之后的例子，你会感觉开窗函数和人类的自然语言很像，获取每个订单、所有订单的运费总和 SELECT orderid, freight, SUM(freight) OVER() AS freightTotal FROM Sales.Orders

透视/逆透视解决方案

解释与示例

标准透视

相信大家都很熟悉这种写法，因为面试中经常问到

SELECT empid, SUM(CASE WHEN custid = 'A' THEN qty END) AS A,

SUM(CASE WHEN custid = 'B' THEN qty END) AS B,

SUM(CASE WHEN custid = 'C' THEN qty END) AS C,

SUM(CASE WHEN custid = 'D' THEN qty END) AS D

FROM dbo.orders

GROUP BY empid;

这儿需要强调的重点是这个解决方案其实涉及3个阶段：第一个阶段为GROUP BY empid分组阶段；第二阶段为扩展阶段通过在SELECT字句中使用针对目标列的CASE表达式；最后一个阶段聚合阶段通过对每个CASE表达式结果聚合，例如SUM。

PIVOT透视

PIVOT实际是一个表运算符，包含分组、扩展、聚合三个逻辑阶段

SELECT empid, A, B, C, D

FROM ( SELECT empid, custid, qty FROM dbo.Orders) AS D PIVOT(SUM(qty) FOR custid IN (A, B, C, D)) AS P

以上可以发现子查询D中，包含empid、custid、qty三个属性，其中custid作为分组属性,qty作为聚合属性，那么剩下的empid就是扩展属性（不显示的指出但可以推算出）

标准逆透视

WITH CTE AS(

SELECT empid, custid, CASE custid WHEN 'A' THEN A WHEN 'B' THEN B WHEN 'C' THEN C END AS qty

FROM dbo.EmpCustOrders CROSS JOIN (VALUES('A'), ('B'), ('C'), ('D')) AS Custs(custid) )

SELECT * FROM CTE WHERE qty IS NOT NULL

逆透视包括也包括三个逻辑阶段：第一阶段需要通过交叉联接生成每一列对应的一个副本；第二阶段通过CASE运算符生成列(qty)；最后一个阶段通过去qty IS NOT NULL删除不相关的交叉点，这一点一定不能忘了。

UNPIVOT逆透视

SELECT empid, custid, qty FROM dbo.EmpCustOrders UNPIVOT(qty FOR custid IN(A, B, C, D)) AS U ，有没有觉得超简单？

GROUPING SETS

SELECT empid, custid, SUM(qty) AS sumqty

FROM dbo.Orders GROUP BY GROUPING SETS((empid, custid), (empid), (custid), ());