sql - 了解 Sql Server 查询 - ORDER BY 子句中的 CASE

Question

我试图在 SELECT< 中使用 DISTINCT 将 CASE 语句放入 ORDER BY 子句中 列表，并发现一些我不理解的奇怪行为。这是一些代码:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'c' then BU
else ISO_ID
end

这有效。但如果我将第四行更改为 when 'b' = 'b' then BU:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'b' then BU
else ISO_ID
end

它因错误而中断:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

当 BU 明显位于选择列表中时。更奇怪的是，当我将代码更改为:

select distinct Requester, ISO_ID as ISO, (ISO_ID - 5 + 50) AS 'someNum', BU
from LoanerHeader order by
CASE WHEN 'a' = 'b' then Requester
when 'b' = 'b' then BU
else BU   --change is here
end

又可以用了!这有什么意义呢？有人可以帮我解决这个问题吗？

Answer 1

CASE 的规则是结果应转换为具有最高优先级的分支的数据类型。

对于第一个查询，它使用矛盾检测并仅生成一个直接按 ISO_ID 排序的计划。这已经是数字，因此不需要隐式转换，因此可以毫无问题地匹配选择列表中的表达式。

对于第二个查询，它可以在编译时再次确定需要ORDER BY BU。但由于上述原因，它实际上需要 ORDER BY CAST(BU AS NUMERIC) 。这意味着它需要 ORDER BY 一个与 SELECT 列表中任何内容都不匹配的计算表达式。这就是问题所在。

您的第三个查询从 CASE 中删除了更高优先级的表达式，从而消除了对隐式转换的需要(因此不需要按计算表达式进行排序)。

由于计算表达式完全依赖于 SELECT DISTINCT 列表中的列，因此您可以按如下方式重写第二个查询。

;WITH CTE AS
(
SELECT DISTINCT Requester,
                ISO_ID              AS ISO,
                ( ISO_ID - 5 + 50 ) AS 'someNum',
                BU
FROM   LoanerHeader
)
SELECT *
FROM CTE
ORDER  BY CASE
            WHEN 'a' = 'b' THEN Requester
            WHEN 'b' = 'b' THEN BU
            ELSE ISO
          END