表表达式的派生表例子

按照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 ]