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

2021-09-10 00:00:00 sql tsql sql-server

我试图在 SELECT<中使用 DISTINCT 将 CASE 语句放在 ORDER BY 子句中/code> 列表，并发现一些我不明白的奇怪行为.这是一些代码:


I'm trying to mess around with putting CASE statement in the ORDER BY clause using a DISTINCT in the SELECT list, and finding some odd behavior that I don't understand. Here is some code:
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

这有效.但是，如果我将第 4 行更改为 when 'b' = 'b' then BU:

This works. But if I change the 4th line to 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 项必须出现在选择列表中，如果 SELECT DISTINCT 是指定.


  ORDER BY items must appear in the select list if SELECT DISTINCT is
  specified.
当BU明显在选择列表中时.更奇怪的是当我将代码更改为:

When BU is obviously in the select list. Even stranger is when I change the code to:
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

它又起作用了！这还有什么意义?有人可以帮我把我的大脑围绕在这个吗?

It works again! How does that even make sense? Can someone help me wrap my brain around this one?
推荐答案
CASE 的规则是将结果强制转换为具有最高优先级的分支的数据类型.

The rules for CASE are that the result should be cast to the datatype of the branch with highest precedence.
对于第一个查询，它使用矛盾检测并直接生成一个按 ISO_ID 排序的计划.这已经是数字，因此无需隐式转换，因此匹配选择列表中的表达式没有问题.

For the first query it uses contradiction detection and just generates a plan that sorts by ISO_ID directly. This is numeric already so no need to implicitly cast and so matches the expression in the select list with no problem.
对于第二个查询，它可以在编译时再次确定它需要ORDER BY BU.除了由于上述原因它实际上需要ORDER BY CAST(BU AS NUMERIC).这意味着它需要 ORDER BY 一个不匹配 SELECT 列表中任何内容的计算表达式.因此问题来了.

For the second query it can again determine at compile time that it needs to ORDER BY BU. Except it actually needs to ORDER BY CAST(BU AS NUMERIC) due to the above. This means it would need to ORDER BY a computed expression not matching anything anything in the SELECT list. Hence the problem.
您的第三个查询从 CASE 中删除了更高优先级的表达式，因此不需要隐式转换(因此不需要按计算表达式排序).

Your third query removes the expression of higher precedence from the CASE thus removing the need for an implicit cast (and hence the need to order by a computed expression). 
由于计算表达式完全依赖于 SELECT DISTINCT 列表中的列，但是您可以按如下方式重写第二个查询.

As the computed expression is entirely dependant upon the columns in the SELECT DISTINCT list however you could rewrite the second query as follows.
;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


	
		相关文章