ORDER BY 子句的 CASE WHEN 语句

我正在使用 SQL Server 2008 R2。

我想要对表中的记录进行基于优先级的排序。

所以我在 ORDER BY 子句中使用 CASE WHEN 语句。 ORDER BY 子句如下：

ORDER BY 
CASE WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount desc, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END, 
CASE WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount desc, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END,
Case WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount DESC, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END,
CASE WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount DESC, TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC END,
Case WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount DESC, TblList.LastName ASC, TblList.FirstName ASC, Patlist.MiddleName ASC END

但它给了Incorrect syntax near the keyword 'desc'

有什么解决办法吗？

我还可以有：

TblList.PinRequestCount <> 0 and TblList.HighCallAlertCount <> 0 and
TblList.HighAlertCount <> 0` and TblList.MediumCallAlertCount <> 0 and  
TblList.MediumAlertCount <> 0 

同时。

CASE is an 表达- 它返回一个single标量值（每行）。它不能返回其他东西的解析树的复杂部分，例如ORDER BY的条款SELECT陈述。

看起来你只需要：

ORDER BY 
CASE WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount END desc,
CASE WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount END desc, 
Case WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount END DESC,
CASE WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount END DESC,
Case WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount END DESC,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC

或者可能：

ORDER BY 
CASE
   WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount
   WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount
   WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount
   WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount
   WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount
END desc,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC

判断以上哪一项（或其他内容）是您正在寻找的内容有点棘手，因为您没有解释了您想要实现的实际排序顺序是什么，并且 b) 未提供任何排序​​顺序样本数据以及预期的结果，我们可以从中尝试deduce您想要实现的实际排序顺序。

这可能就是我们正在寻找的答案：

ORDER BY 
CASE
   WHEN TblList.PinRequestCount <> 0 THEN 5
   WHEN TblList.HighCallAlertCount <> 0 THEN 4
   WHEN TblList.HighAlertCount <> 0 THEN 3
   WHEN TblList.MediumCallAlertCount <> 0 THEN 2
   WHEN TblList.MediumAlertCount <> 0 THEN 1
END desc,
CASE
   WHEN TblList.PinRequestCount <> 0 THEN TblList.PinRequestCount
   WHEN TblList.HighCallAlertCount <> 0 THEN TblList.HighCallAlertCount
   WHEN TblList.HighAlertCount <> 0 THEN TblList.HighAlertCount
   WHEN TblList.MediumCallAlertCount <> 0 THEN TblList.MediumCallAlertCount
   WHEN TblList.MediumAlertCount <> 0 THEN TblList.MediumAlertCount
END desc,
TblList.LastName ASC, TblList.FirstName ASC, TblList.MiddleName ASC