主页 / dba / 问题 / 221290
Accepted
BradC
Asked: 2018-10-30 12:28:27 +0800 CST

将没有列的 ROW_NUMBER() 添加到 ORDER BY？

  • 772

所以我正在研究一个代码高尔夫拼图，需要在结果中添加一个 INT“数字”列n，同时保持当前顺序。

假设我的源数据是：

SELECT value
FROM STRING_SPLIT('one,two,three,four,five', ',')

它以原始（所需）顺序返回项目：

value
-----
one
two
three
four
five

如果我尝试使用ROW_NUMBER()或者RANK()我被迫指定一个ORDER BY，这value是唯一合法的选择：

SELECT value, n = ROW_NUMBER() OVER(ORDER BY value)
FROM STRING_SPLIT('one,two,three,four,five',',')

但这（如预期的那样）按value字母顺序排序，而不是按所需的原始顺序排列：

value   n
------ ---
five    1
four    2
one     3
three   4
two     5

加入数字表是行不通的，因为没有WHERE子句我会得到一个完整的外连接。

我能想到的最好的方法是使用带有标识字段的临时表：

CREATE TABLE #argg (n INT IDENTITY(1,1), v VARCHAR(99))

INSERT #argg 
SELECT value v
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT *
FROM #argg

DROP TABLE #argg

但这真的很长而且很烦人。有更好的想法吗？

sql-server order-by

4 个回答

  1. Best Answer

    执行此操作的规范方法如下ROW_NUMBER() OVER(ORDER BY (SELECT NULL))：如果你在打高尔夫球，你可以尝试这样的事情：

    SELECT value, n = ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM STRING_SPLIT('one,two,three,four,five',',')

    它适用于您在问题中发布的简单案例：

    在此处输入图像描述

    我应该说，没有文件保证该ROW_NUMBER()值将按照您期望的精确顺序。但这是代码高尔夫，所以这似乎已经足够好了。

    • 11

  2. 您不能依赖您INSERT按原始字符串的顺序生成IDENTITY值。这可能是你观察到的，但这只是幸运的巧合，当然不能保证。

    如果您没有重复项，以下将起作用：

    DECLARE @str varchar(255) = 'one,two,three,four,five';

SELECT value, n = ROW_NUMBER() OVER
  (ORDER BY CHARINDEX(',' + value + ',', ',' + @str + ','))
FROM STRING_SPLIT('one,two,three,four,five', ',')
ORDER BY n;

    对于代码高尔夫也许：

    DECLARE @s char(99)='one,two,three,four,five';
SELECT value,n=RANK() OVER(ORDER BY CHARINDEX(','+value+',',','+@s+','))
FROM STRING_SPLIT('one,two,three,four,five', ',')ORDER BY n

    如果你有重复，它变得更加复杂。这里的一些想法可能是：

    • 6

  3. 另一种选择是使用序列：

    DROP SEQUENCE IF EXISTS dbo.S;
CREATE SEQUENCE dbo.S START WITH 1;

SELECT value, NEXT VALUE FOR dbo.S 
FROM STRING_SPLIT('one,two,three,four,five', ',');

    输出：

    ╔═══════╦═══╗
║ one   ║ 1 ║
║ two   ║ 2 ║
║ three ║ 3 ║
║ four  ║ 4 ║
║ five  ║ 5 ║
╚═══════╩═══╝

    STRING_SPLIT在没有内置编号选项的情况下实现令人讨厌。在https://feedback.azure.com/forums/908035-sql-server/suggestions/32902852-string-split-is-not-feature-complete投票支持更改

    • 5

  4. 只需从您的问题中添加另一种选择。但我认为不能保证订购，

    SELECT value v,IDENTITY(INT,1,1) AS n
INTO #argg
FROM STRING_SPLIT('one,two,three,four,five',',')

SELECT * FROM #argg
    • 2

相关问题