主页 / dba / 问题 / 305155
Accepted
HeyJude
Asked: 2021-12-20 02:40:02 +0800 CST

按零排序如何工作

  • 772

在以下脚本中（请参阅此处的完整演示）：

SELECT *
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
   END
  • 的类型是0什么？
  • 是如何0解释的？不是选择列表中列的序号，因为选择列表枚举是从 1 开始的，而不是 0。那么它是什么？

为了详细说明，将上面的代码段与以下代码段进行比较，它会发出错误：

SELECT *
FROM Table1
ORDER BY 0

ORDER BY 位置编号 0 超出了选择列表中的项目数范围。

并将其与以下代码段进行比较，该代码段会发出另一个错误：

SELECT *
FROM Table1
ORDER BY '0'

在 ORDER BY 列表的位置 1 中遇到常量表达式。

似乎将0值嵌套在case子句下使它起作用，但我不明白为什么以及如何？

  • 这个问题在某种程度上是这个旧问题的后续问题，与我上面列出的替代方案相比，它没有解释为什么它可以工作： 如何使用 order by 0 工作 SQL？
sql-server order-by

3 个回答

  1. ORDER BY 条款文档指出：

    order_by_expression

    指定对查询结果集进行排序的列或表达式。排序列可以指定为名称或列别名，或表示列在选择列表中的位置的非负整数。

    可以说，该文档可以更清楚地说明这一点。根据第二句，“列”可以指定为：

    • 列名
    • 列别名
    • 列位置

    CASE是“表达式”而不是列规范。该表达式未指定排序顺序列。表达式结果的值用于排序。

    表达式的结果CASE WHEN Field1 IS NOT NULL THEN 0 END是一个整数（由于整数常量为零），其值为 0 或NULL。表达式类型信息可以通过以下方式收集sys.dm_exec_describe_first_result_set：

    SELECT system_type_name, is_nullable
FROM sys.dm_exec_describe_first_result_set(N'SELECT CASE WHEN Field1 IS NOT NULL THEN 0 END FROM dbo.Table1',NULL,NULL);

+------------------+-------------+
| system_type_name | is_nullable |
+------------------+-------------+
| int              |           1 |
+------------------+-------------+

    使用稍微修改的数据和有效的 CASE 表达式，您可以看到具有 NULL 值的行在具有非空值的行之前排序，因为 CASE 表达式为具有 NULL Field1 值的行返回 NULL，而对于具有 NULL 值的行返回零非空值。

    CREATE TABLE Table1(ID INT IDENTITY(1,1), Field1 INT, FieldSomethingElse VARCHAR(10));
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (1, 'some');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (765, 'or');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (2, 'thing');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'another');
INSERT INTO Table1 (Field1, FieldSomethingElse) VALUES (NULL, 'thing');

SELECT *,CASE WHEN Field1 IS NOT NULL THEN 0 END AS ValueUsedForSorting
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
   END;


+----+--------+--------------------+---------------------+
| ID | Field1 | FieldSomethingElse | ValueUsedForSorting |
+----+--------+--------------------+---------------------+
|  4 | NULL   | another            | NULL                |
|  5 | NULL   | thing              | NULL                |
|  1 | 1      | some               | 0                   |
|  3 | 765    | or                 | 0                   |
|  2 | 2      | thing              | 0                   |
+----+--------+--------------------+---------------------+

    我要补充一点，因为无论如何 NULL 都在非 NULL 值之前排序，所以可以简单地按 Field1 排序以提供所需的排序行为，也可能利用列上的索引。与 CASE 表达式不同，此方法还将根据 Field1 值而不是 CASE 表达式零值的未定义顺序（由于零值的关系）对非 NULL 值进行排序。

    SELECT *,Field1 AS ValueUsedForSorting
FROM Table1
ORDER BY Field1;

+----+--------+--------------------+---------------------+
| ID | Field1 | FieldSomethingElse | ValueUsedForSorting |
+----+--------+--------------------+---------------------+
|  4 | NULL   | another            | NULL                |
|  5 | NULL   | thing              | NULL                |
|  1 | 1      | some               | 1                   |
|  3 | 2      | thing              | 2                   |
|  2 | 765    | or                 | 765                 |
+----+--------+--------------------+---------------------+
    • 7
  2. Best Answer

    当您使用表达式（例如CASE）时，它不再按列名或列序号排序，而是现在按值排序。在这种情况下，您选择的值是 0（仅适用于非空Field1行），但它可能是 1、100、'Red' 甚至FieldSomethingElse.

    如果你CASE进一步构建你的表达，你会明白我的意思：

    SELECT *
FROM Table1
ORDER BY 
   CASE 
       WHEN Field1 > 10 
           THEN 100 
        WHEN Field1 > 5
            THEN 200
        WHEN Field1 IS NOT NULL
            THEN 300
        ELSE 900
   END

    Field1以上根据 的值是否大于某个值对行进行半降序排序。它还有一个默认情况，即ELSE子句，它将所有行放在Field1 IS NULL结果的末尾。

    显然，没有名称或序数为 100、200、300 或 900 的列，但上述CASE表达式有效，因为 SQL Server 按子句中满足该子句的行的值对行进行排序。THENWHEN

    当您使用 0 时，在您的示例子句中发生了同样的事情CASE，它只是对常量值 0 上的非空行进行排序。这显然在排序方面没有多大意义，除了所有非空行都将被排序一起（因为它们都将按相同的值 0 排序，因此它们在该排序中的顺序将是不确定的）并且所有空行也将一起排序。

    • 4

  3. order 语句根据 case 条件生成一个包含 NULL 或 0 的“虚拟字段”。

    基于该虚拟字段应用 ORDER BY。

    order by 中的零值在 NULL 值之前。就是这样，这是一种在订单列表末尾设置 field1 的 NULL 的方法。

    尝试以这种方式可视化它：

    SELECT *,
CASE WHEN Field1 IS NOT NULL 
     THEN 0 
  END as Filed1Order
FROM Table1
ORDER BY 
   CASE WHEN Field1 IS NOT NULL 
     THEN 0 
  END
    • 0

相关问题