T-SQL 语言基础：表表达式_

介绍

派生表是嵌套在 FROM 子句中的子查询。它们的作用范围仅限于包含它们的查询。派生表可以简化查询逻辑，解决复杂问题，并规避某些语言限制。

派生表的定义

表表达式是一种命名的查询表达式，代表一个有效的关系表，可以像其他表一样在数据处理语句中使用。SQL 支持四种类型的表表达式：派生表、公用表表达式、视图和内联表值函数。

示例

分配列别名

SELECT ORDERYEAR, COUNT(DISTINCT custid) AS NUMCUSTS
FROM (
    SELECT YEAR(orderdate) AS ORDERYEAR, custid
    FROM Sales.Orders
) AS D
GROUP BY ORDERYEAR;

输出：

image_alt_text

嵌套

返回订单年份和该年处理的客户数，但要求每个订单年份处理的客户要多于 70 人：

方法一

SELECT orderyear, numCusts 
FROM (
		-- 聚合计算 custid，拿到当年的订单数量
		SELECT orderyear, COUNT(DISTINCT custid) AS numCusts
		FROM (
				-- 处理 OrderYear，拿到订单年份
				SELECT YEAR(orderdate) AS orderyear, custid
				FROM Sales.Orders 
			) O1
		GROUP BY orderyear
	) D2
WHERE numCusts > 70;

方法二

SELECT orderyear, numCusts 
FROM (
	SELECT orderyear, COUNT(DISTINCT custid) AS numCusts
	FROM (
			SELECT YEAR(orderdate) AS orderyear, custid
			FROM Sales.Orders 
		) O1
	GROUP BY orderyear
	HAVING COUNT(DISTINCT custid) > 70
) D2;

输出

image_alt_text

在这里插入图片描述更喜欢哪种方法？

多引用

-- 派生表另一个存在问题的方面源于派生表时在外部查询的 FROM 子句中定义的，其逻辑处理顺序并不优先于外部查询
-- 当对外部查询的 FROM 子句进行处理时，派生表其实并不存在
-- 因此，如果须要引用派生表的多个实例，需基于同一查询去定义多个派生表

-- 基于同一查询而定义的多个派生表
SELECT 
	CUR.orderYear,
	CUR.numCusts				AS CURnumCusts,
	PRV.numCusts				AS PRVnumCusts,
	CUR.numCusts - PRV.numCusts AS Growrth
FROM (
	SELECT 
		YEAR(orderdate)			AS orderYear, 
		COUNT(DISTINCT custid)	AS numCusts
	FROM Sales.Orders 
	GROUP BY YEAR(orderdate)
) AS CUR
LEFT JOIN(
	SELECT 
		YEAR(orderdate)			AS orderYear, 
		COUNT(DISTINCT custid)	AS numCusts
	FROM Sales.Orders 
	GROUP BY YEAR(orderdate)
) AS PRV
ON CUR.orderYear = PRV.orderYear + 1;