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