ORDER BY子句中的CASE语句

Question

SQL SERVER

我正试图以精确的方式对ORDERBY子句中的记录进行排序。因此，应该按照以下方式对记录进行排序。我想我的问题可能是CASE语句语法，但除了代码没有运行之外，我似乎找不到任何东西告诉我它错了。

od.状态商行在过程中，释放，其他所有

我相信我可以为每种类型的记录分配一个数字，然后对这些数字进行排序。

下面的代码给了我"；如果指定select DISTINCT，ORDER BY项目必须出现在选择列表中；

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%'

ORDER BY
CASE 
WHEN od.Status = 'Firm' THEN 1
WHEN od.Status = 'In Process' THEN 2
WHEN od.Status = 'Released' THEN 3
ELSE 4
END,
vsc.Email_Address ASC, 
CONVERT(char(10),od.Projected_Ship_Date,101) ASC

如有任何帮助，我们将不胜感激。我在这个问题上找不到太多。我发现的大多数问题都希望对一个集合DESC和另一个集合ASC进行排序，但不是按照特定的顺序。

感谢

Answer 1

刚刚更新了您的查询：

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 StatusOrderId
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
CASE 
WHEN od.Status = 'Firm' THEN 1
WHEN od.Status = 'In Process' THEN 2
WHEN od.Status = 'Released' THEN 3
ELSE 4
END,
vsc.Email_Address ASC, 
CONVERT(char(10),od.Projected_Ship_Date,101) ASC

Answer 2

问题是"case"；order by中的语句实际上必须显示为select语句中的列，因为您正在执行select distinct。这里有一个例子，第一个查询无效，第二个工作

select    distinct 
tableName = t.name
from      sys.tables t
order by  case 
when t.name like '%something%' then 1
else 2
end;

select    distinct 
orderingColumn = case 
when t.name like '%something%' then 1
else 2
end,
tableName      = t.name
from      sys.tables t
order by  case 
when t.name like '%something%' then 1
else 2
end

这里的问题是，orderingColumn将由select语句返回，看起来你不希望这样，但这很容易通过CTE或子查询解决：

with MyQuery as 
(
select    distinct 
orderingColumn = case 
when t.name like '%something%' then 1
else 2
end,
tableName      = t.name
from      sys.tables t
)
select tableName from MyQuery order by orderingColumn;