SQL Server ORDER BY用CASE自定义排序搭配SELECT DISTINCT报错咨询

当SQL语句使用SELECT DISTINCT去重时，数据库引擎需要先对结果集做去重计算，此时所有用于排序的字段/计算表达式必须包含在最终的查询输出列表中，否则引擎无法确认排序逻辑对应的数值是否在去重后的结果中存在对应关系。你当前ORDER BY子句中的CASE自定义排序表达式没有出现在SELECT列表中，因此触发了该报错。

有两种常用的解决方式，可根据需求选择：

方案1：将自定义排序表达式加入SELECT列表 #

改动最小，只需要在SELECT的字段列表中新增CASE表达式的输出即可，如果不想让这个排序序号出现在最终业务返回结果中，可后续在业务层忽略该字段。
修改后的完整代码如下：

SELECT DISTINCT
oh.Order_Number AS Order_Number,
oh.Status AS Order_Status,
oh.Customer_Name AS Customer_Name,
vsc.Salesman_Name AS Salesman_Name,
vsc.Email_Address AS Email_Address,
od.Work_Code AS Work_Code,
od.Product_Code AS Product_Code,
CONVERT(char(10),od.Projected_Ship_Date,101) AS Projected_Ship_Date,
CONVERT(char(10),od.Due_Date,101) AS OD_Due_Date,
format(oh.Gross_Amount, '$#,##0.##') AS Gross_Amount,
DATEDIFF(DAY,oh.Order_Date,'{%Current Date%}') AS DIP,
od.Part_Number AS Part_Number,
od.Status AS Status,
CAST(qd.Delivery_Notes AS NVARCHAR(MAX)) AS Delivery_Notes,
-- 新增排序用的自定义序号字段
CASE 
WHEN od.Status = 'Firm' THEN 1
WHEN od.Status = 'In Process' THEN 2
WHEN od.Status = 'Released' THEN 3
ELSE 4
END AS Sort_Order
FROM
dbo.Order_Header oh LEFT OUTER JOIN dbo.Commission_Distribution cd ON oh.Order_Header_ID = cd.Order_Header_ID LEFT OUTER JOIN 
dbo.vSalesman_Code vsc ON cd.Salesman_Code = vsc.Salesman_Code JOIN
dbo.Order_Detail od ON od.Order_Header_ID = oh.Order_Header_ID JOIN
dbo.Quotation_Detail qd ON od.Quotation_Detail_ID = qd.Quotation_Detail_ID JOIN
dbo.Quotation_Header qh ON qd.Quotation_Header_ID = qh.Quotation_Header_ID
WHERE
	oh.Status  =  'Open' AND
cd.Company_Code  =  'AIN' AND
oh.Customer_Name  NOT IN  ( 'A.I. Innovations' , 'AI PROPERTIES Fortville LLC' , 'AI-IN Intercompany' , 'AI-NC Intercompany' ) AND
od.Status  <>  'Closed' AND
LEFT(od.Part_Number, 3)  <>  'MTS' AND
vsc.Salesman_Name  NOT IN  ( 'House' , 'House Accounts' ) AND
od.Status  <>  'Hold' AND
od.Product_Code NOT LIKE '%PROCES%' AND
od.Product_Code NOT LIKE '%VISTA WARRANT%'
ORDER BY
Sort_Order,
vsc.Email_Address ASC, 
Projected_Ship_Date ASC

方案2：用子查询先做去重，外层再排序 #

如果不想在输出结果中额外增加排序序号字段，可以先在内层子查询完成去重，外层查询再做排序，这种方式返回的字段和最初需求完全一致：

SELECT * FROM (
    SELECT DISTINCT
    oh.Order_Number AS Order_Number,
    oh.Status AS Order_Status,
    oh.Customer_Name AS Customer_Name,
    vsc.Salesman_Name AS Salesman_Name,
    vsc.Email_Address AS Email_Address,
    od.Work_Code AS Work_Code,
    od.Product_Code AS Product_Code,
    CONVERT(char(10),od.Projected_Ship_Date,101) AS Projected_Ship_Date,
    CONVERT(char(10),od.Due_Date,101) AS OD_Due_Date,
    format(oh.Gross_Amount, '$#,##0.##') AS Gross_Amount,
    DATEDIFF(DAY,oh.Order_Date,'{%Current Date%}') AS DIP,
    od.Part_Number AS Part_Number,
    od.Status AS Status,
    CAST(qd.Delivery_Notes AS NVARCHAR(MAX)) AS Delivery_Notes
    FROM
    dbo.Order_Header oh LEFT OUTER JOIN dbo.Commission_Distribution cd ON oh.Order_Header_ID = cd.Order_Header_ID LEFT OUTER JOIN 
    dbo.vSalesman_Code vsc ON cd.Salesman_Code = vsc.Salesman_Code JOIN
    dbo.Order_Detail od ON od.Order_Header_ID = oh.Order_Header_ID JOIN
    dbo.Quotation_Detail qd ON od.Quotation_Detail_ID = qd.Quotation_Detail_ID JOIN
    dbo.Quotation_Header qh ON qd.Quotation_Header_ID = qh.Quotation_Header_ID
    WHERE
        oh.Status  =  'Open' AND
    cd.Company_Code  =  'AIN' AND
    oh.Customer_Name  NOT IN  ( 'A.I. Innovations' , 'AI PROPERTIES Fortville LLC' , 'AI-IN Intercompany' , 'AI-NC Intercompany' ) AND
    od.Status  <>  'Closed' AND
    LEFT(od.Part_Number, 3)  <>  'MTS' AND
    vsc.Salesman_Name  NOT IN  ( 'House' , 'House Accounts' ) AND
    od.Status  <>  'Hold' AND
    od.Product_Code NOT LIKE '%PROCES%' AND
    od.Product_Code NOT LIKE '%VISTA WARRANT%'
) t
ORDER BY
CASE 
WHEN t.Status = 'Firm' THEN 1
WHEN t.Status = 'In Process' THEN 2
WHEN t.Status = 'Released' THEN 3
ELSE 4
END,
t.Email_Address ASC, 
t.Projected_Ship_Date ASC

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