理解Sql Server查询 - 在ORDER BY子句中使用CASE

Question

我在尝试使用DISTINCT在SELECT列表中，将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

错误提示：

如果指定了SELECT DISTINCT，则ORDER BY项目必须出现在选择列表中。

当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

它又能工作了！这怎么可能呢？有人可以帮我理解一下吗？

在技术层面上，我们需要进一步检查并诊断问题。

- Nick Rolando

---

Requester、BU 和 ISO_ID 的数据类型是什么？ - Martin Smith

@MartinSmith 分别是 nvarchar、nvarchar、numeric 类型。在这种情况下，好像不能按 BU 或 Requester 排序。我觉得你说的有点道理。 - Nick Rolando

Answer 1

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

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

对于第二个查询，编译时可以确定它需要按 BU 进行排序。但是，由于上述原因，它实际上需要按 CAST(BU AS NUMERIC) 进行排序。这意味着它需要按计算表达式进行排序，而不是与 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

- Martin Smith

Answer 2

你必须在 SELECT 列表中包含完整的 CASE 表达式，而不仅仅是组成部分。

如果 Requester、BU 和 ISO_ID 的数据类型不同，则可能会出现我们在此处观察到的不稳定行为。从示例中可以看出，ISO_ID 是数字类型。根据行为推测，Requester 和 BU 都不是数字类型，但它们都是相同的类型（或兼容的类型）。如果它们都是字符类型，则可能需要将 ISO_ID 转换为字符类型，在 CASE 表达式中使用。

- WReach

---

+1 这似乎是问题所在。尽管将 ISO_ID 强制转换为 nvarchar 并不能解决它。但如果我在 select * 和 case 中用另一个 nvarchar 列（例如 Company）替换 ISO_ID，所有情况都能正常工作 :) 不过很好奇，如果只是指定要按列排序，为什么所有情况都必须是相同的类型？ - Nick Rolando

顺便提一下，假设我不想在选择列中包含该案例，因为我不想在输出中看到它。该方法在此页面底部进行了示例演示：http://www.4guysfromrolla.com/webtech/102704-1.shtml - Nick Rolando

很抱歉，强制转换确实解决了这个问题 :) 我必须在select和order by中都进行强制转换，而且不能使用任何别名。 - Nick Rolando

Answer 3

我在 SQL Server 2000 中运行了类似于你的代码，但每次都失败了。当我转到 SQL Server 2005 时，开始出现了你的问题。我将代码更改为以下形式：

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

我遇到了一个错误，如下所示：

将 nvarchar 值“xxxxxxxxx”转换为数据类型 tinyint 时转换失败。

我不知道你的数据类型是什么，但似乎它们都必须相同，并且当未指定字段的类型时，它似乎将 else 部分的字段的数据类型作为所有字段的类型。

当我做类似以下的操作时：

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

然后在两种情况下更改字母，似乎运行良好。

可能更好的编码方式是：

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

- David Parvin

Answer 4

我之前写过一篇博客，其中有一个非常简单的例子，包括代码和对问题的全面解释。 SQL Server Case/When Data Type Problems

- George Mastros

---

很酷，谢谢。虽然我对为什么CASE的所有分支需要是相同类型感兴趣。我不明白为什么想要返回不同类型会有什么大问题。也许我会问另一个问题:p - Nick Rolando