ORDER BY 子句的 CASE WHEN 语句

Question

我正在使用 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

同时。

原文由 Dev 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

CASE 是一个 表达式- 它返回 单个 标量值（每行）。它不能返回解析树的复杂部分，例如 --- 语句的 SELECT ORDER BY 子句。

看起来你只需要：

 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

判断上述哪个（或其他）是您要查找的内容有点棘手，因为您 a) 没有 解释 您要实现的实际排序顺序，并且 b) 没有提供任何 示例数据 和预期的结果，我们可以从中尝试 推断 您尝试实现的实际排序顺序。

---

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

 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

原文由 Damien_The_Unbeliever 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

也许您可以使用 WITH 子句：

 WITH request AS (
    SELECT id
         , CASE
               WHEN a > 100 THEN 1
               ELSE 0
        END AS example
    FROM table
)
SELECT *
FROM request
ORDER BY example DESC

它也适用于 WHERE 子句

原文由 ledukilian 发布，翻译遵循 CC BY-SA 4.0 许可协议