sql-server - 排序是按实际列还是别名进行的？

Question

假设我有一个像这样的简单查询:

select 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade, 
    count(*) as 'count' 
From table_empl 
where year(EnterDate) = year(getdate())  
group by subgroup, grade  
order by grade  

似乎order bygrade是按别名grade而不是实际列grade;排序的，至少结果是这样的显示。

这是正确的吗？

由于我无法更改结果中包含的列，是否可以向实际列添加别名？像这样的吗？

select 
    grade as 'grade2', 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade,
    count(*) as 'count'
From table_empl 
where year(EnterDate) = year(getdate())  
group by subgroup,grade  
order by grade2

Answer 1

如果在 ORDER BY 子句中为其列名添加表名(或在 FROM 子句中为表指定的别名)作为前缀，那么它将使用列，而不是在 SELECT 子句中计算并与列同名的表达式。

所以这应该使用原始的grade列进行排序:

select 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade, 
    count(*) as 'count' 
From table_empl 
where year(EnterDate) = year(getdate())  
group by subgroup, grade  
order by table_empl.grade

或者:

select 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade, 
    count(*) as 'count' 
From table_empl t
where year(EnterDate) = year(getdate())  
group by subgroup, grade  
order by t.grade