SQL Server语言 - ROW_NUMBER空值分区的行为

Question

我觉得这种行为非常奇怪，违反直觉。(即使是SQL).

set ansi_nulls off
go
;with sampledata(Value, CanBeNull) as
(
  select 1, 1
  union 
  select 2, 2
  union 
  select 3, null
  union 
  select 4, null
  union 
  select 5, null
  union 
  select 6, null
)
select ROW_NUMBER() over(partition by CanBeNull order by      value) 'RowNumber',* from sampledata

返回

1   3   NULL
2   4   NULL
3   5   NULL
4   6   NULL
1   1   1
1   2   2

这意味着为了计算行号，所有的null都被视为同一组的一部分。SET ANSI_NULLLS是打开还是关闭并不重要。但根据定义，空值是完全未知的那么空值怎么能像这样组合在一起呢?它是说，为了将事物按顺序排列，苹果，橘子，负1的平方根，量子黑洞或任何东西都可以有意义地排序。一个小实验表明，第一列被用来生成

的秩顺序。

  select 1, '1'
  union 
  select 2, '2'
  union 
  select 5, null
  union 
  select 6, null
  union
  select 3, null
  union 
  select 4, null

生成相同的值。这对我正在处理的遗留代码产生了重大影响。这是预期的行为吗?除了用唯一值替换选择查询中的null之外，还有什么方法可以减轻这种行为吗?

我所期望的结果应该是

1   3   NULL
1   4   NULL
1   5   NULL
1   6   NULL
1   1   1
1   2   2

使用Dense_Rank()没有区别

Answer 1

Yo.

因此，当T-SQL处理谓词中的null时，它使用三元逻辑(TRUE、FALSE或UNKNOWN)并显示您所声明的期望从查询中得到的行为。但是，在对值进行分组时，T-SQL将null视为一组。因此，您的查询将把null分组在一起，并开始对该窗口内的行进行编号。

对于你说你想看到的结果，这个查询应该工作…

WITH sampledata (Value, CanBeNull)
AS
(
    SELECT 1, 1
    UNION
    SELECT 2, 2
    UNION
    SELECT 3, NULL
    UNION
    SELECT 4, NULL
    UNION
    SELECT 5, NULL
    UNION
    SELECT 6, NULL
)
SELECT
    DENSE_RANK() OVER (PARTITION BY CanBeNull ORDER BY CASE WHEN CanBeNull IS NOT NULL THEN value END ASC) as RowNumber
    ,Value
    ,CanBeNull
FROM sampledata