如何按未在SELECT子句中指定的列排序且不显示该列？

你遇到的报错是因为SQL严格模式要求：ORDER BY中引用的列必须包含在SELECT列表、聚合函数里，或者出现在GROUP BY子句中。以下是几种合法的实现方式，结合你的查询语句举例：

方法1：将排序列加入GROUP BY子句 #

如果要排序的列和当前GROUP BY的列逻辑关联（比如每个分组对应唯一的该列值），直接把它加到GROUP BY里，就能在ORDER BY中使用，不需要在SELECT里展示。

假设你要按Pt.ProjectID排序，修改后的查询如下：

select Pr.EmployeeNo as EmpNo, EmployeeFName as EmpFName, EmployeeLName as EmpLName,
ProjectName, ProjectStartDate as ProjStartDate, JobName as Job, JobRate, HoursWorked as Hours
from Employee as Em join ProjEmp as Pr on Em.EmployeeNo = Pr.EmployeeNo
                    join Project as Pt on Pr.ProjectID = Pt.ProjectID
                    join Job as Jb on Em.JobID = Jb.JobID
Group by Pr.EmployeeNo, EmployeeFName, EmployeeLName, ProjectName, ProjectStartDate, JobName, JobRate, HoursWorked, Pt.ProjectID
ORDER BY Pt.ProjectID

方法2：通过子查询/CTE间接引用排序列 #

先在子查询中包含需要的展示列和排序列，外层SELECT只提取要展示的内容，再按子查询里的排序列排序。

示例：

SELECT EmpNo, EmpFName, EmpLName, ProjectName, ProjStartDate, Job, JobRate, Hours
FROM (
    select Pr.EmployeeNo as EmpNo, EmployeeFName as EmpFName, EmployeeLName as EmpLName,
    ProjectName, ProjectStartDate as ProjStartDate, JobName as Job, JobRate, HoursWorked as Hours,
    Pt.ProjectID as SortCol
    from Employee as Em join ProjEmp as Pr on Em.EmployeeNo = Pr.EmployeeNo
                        join Project as Pt on Pr.ProjectID = Pt.ProjectID
                        join Job as Jb on Em.JobID = Jb.JobID
    Group by Pr.EmployeeNo, EmployeeFName, EmployeeLName, ProjectName, ProjectStartDate, JobName, JobRate, HoursWorked, Pt.ProjectID
) AS Temp
ORDER BY SortCol

方法3：用聚合函数包裹排序列 #

如果每个分组内要排序的列值是一致的，可用MAX()或MIN()这类聚合函数包裹它，这样无需加入GROUP BY就能在ORDER BY中使用，且不影响排序结果。

示例：

select Pr.EmployeeNo as EmpNo, EmployeeFName as EmpFName, EmployeeLName as EmpLName,
ProjectName, ProjectStartDate as ProjStartDate, JobName as Job, JobRate, HoursWorked as Hours
from Employee as Em join ProjEmp as Pr on Em.EmployeeNo = Pr.EmployeeNo
                    join Project as Pt on Pr.ProjectID = Pt.ProjectID
                    join Job as Jb on Em.JobID = Jb.JobID
Group by Pr.EmployeeNo, EmployeeFName, EmployeeLName, ProjectName, ProjectStartDate, JobName, JobRate, HoursWorked
ORDER BY MAX(Pt.ProjectID)

内容的提问来源于stack exchange，提问作者Arthwys Ironhand