表表达式的派生表例子

按照SQL的逻辑查询处理顺序，Group BY子句是先于SELECT子句处理。

平时写法(YEAR(birthday))：

SELECT YEAR(birthday) AS [Year], AVG([Salary]) AS [AvgSalary] FROM [dbo].[Member]
GROUP BY YEAR(birthday)

 

只能使用SELECT一样的写法。是否可以在GROUP BY中使用SELECT阶段的名别呢，答案是肯定的。使用派生表，可以实现外部查询的子句都可以引用在内部查询的SELECT子句中分配的列别名。参考重构例子：

SELECT [Year],AVG([Salary]) AS [AvgSalary]
FROM (SELECT YEAR(birthday) AS [Year],[Salary] FROM [dbo].[Member]) AS M
GROUP BY [YEAR]