时间戳

首页 > 解决方案 > 为什么在 INSERT INTO 命令中使用 ROW_NUMBER() 的行为与预期不同？

sql - 为什么在 INSERT INTO 命令中使用 ROW_NUMBER() 的行为与预期不同？

问题描述

这个例子有点复杂，但基本上我有一个表，里面有一些现有的数据，我想根据我传入的 Id 的顺序重新排序（更新列值）。

如果我运行这组 SQL 命令：

DECLARE @Ids VARCHAR(MAX) = 'D,E,B,A,C'

DECLARE @Table TABLE (
    Id          VARCHAR(2),
    SortRank    INT,
    OptionValue INT
)

INSERT INTO @Table (Id, SortRank, OptionValue) VALUES
    ('A', 1, 1),
    ('B', 2, 1),
    ('C', 3, 3),
    ('D', 4, 2),
    ('E', 5, 3)

-- Storing existing values in table variable so we can re-insert records in new order without losing existing OptionValue values
DECLARE @Existing TABLE (
    Id          VARCHAR(2),
    OptionValue INT
)

INSERT INTO @Existing (Id, OptionValue)
    SELECT  Id, OptionValue
    FROM    @Table

DELETE
FROM    @Table

INSERT INTO @Table (Id, SortRank, OptionValue)
    SELECT      I.Id, I.SortRank, E.OptionValue
    FROM        (
                    SELECT  value AS Id, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SortRank
                    FROM    STRING_SPLIT(@Ids, ',')
                ) AS I
                INNER JOIN @Existing AS E ON E.Id = I.Id
    ORDER BY    I.SortRank ASC

SELECT  *
FROM    @Table

我得到以下输出（这与我第一次插入@Table 的内容相同）：

+----+----------+-------------+
| Id | SortRank | OptionValue |
+----+----------+-------------+
| A  |        1 |           1 |
| B  |        2 |           1 |
| C  |        3 |           3 |
| D  |        4 |           2 |
| E  |        5 |           3 |
+----+----------+-------------+

但是，我期望得到的是：

+----+----------+-------------+
| Id | SortRank | OptionValue |
+----+----------+-------------+
| D  |        1 |           2 |
| E  |        2 |           3 |
| B  |        3 |           1 |
| A  |        4 |           1 |
| C  |        5 |           3 |
+----+----------+-------------+

我可以通过使用 ROW_NUMBER() 函数将查询中的数据持久保存到临时表中，然后在单独的操作中将其插入主表中，如下所示：

DECLARE @Ids VARCHAR(MAX) = 'D,E,B,A,C'

DECLARE @Table TABLE (
    Id          VARCHAR(2),
    SortRank    INT,
    OptionValue INT
)

INSERT INTO @Table (Id, SortRank, OptionValue) VALUES
    ('A', 1, 1),
    ('B', 2, 1),
    ('C', 3, 3),
    ('D', 4, 2),
    ('E', 5, 3)

-- Storing existing values in table variable so we can re-insert records in new order without losing existing OptionValue values
DECLARE @Existing TABLE (
    Id          VARCHAR(2),
    OptionValue INT
)

INSERT INTO @Existing (Id, OptionValue)
    SELECT  Id, OptionValue
    FROM    @Table

DELETE
FROM    @Table


SELECT      I.Id, I.SortRank, E.OptionValue
INTO        #Table
FROM        (
                SELECT  value AS Id, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS SortRank
                FROM    STRING_SPLIT(@Ids, ',')
            ) AS I
            INNER JOIN @Existing AS E ON E.Id = I.Id
ORDER BY    I.SortRank ASC

INSERT INTO @Table (Id, SortRank, OptionValue)
    SELECT  Id, SortRank, OptionValue
    FROM    #Table

SELECT  *
FROM    @Table

我无法弄清楚为什么第二种方法有效，而第一种方法失败了，尤其是因为逻辑基本相同，但我怀疑这与我对 ROW_NUMBER() 计算方式的理解有关。

您可以提供的任何见解将不胜感激。

标签： sqlsql-server

解决方案

您的代码存在三个问题。你有：

  • insertinto@table不插入任何行，因为它@existing是空的。所以没有对表进行任何修改。
  • 没有的SELECT查询ORDER BY。结果的顺序不定。
  • 该string_split()函数不以确定的顺序返回值。

SQL 表表示无序集。

如果字符串中没有重复项，则可以修复select：

    SELECT I.Id, I.SortRank, E.OptionValue
    FROM (SELECT value AS Id,
                 ROW_NUMBER() OVER (ORDER BY CHARINDEX(',' + s.value + ',', ',' + @ids + ',')) AS SortRank
          FROM STRING_SPLIT(@Ids, ',') s
         ) I JOIN
         @Existing E
         ON E.Id = I.Id
    ORDER BY I.SortRank ASC;

我认为这是您尝试编写的完整代码：

DECLARE @Ids VARCHAR(MAX) = 'D,E,B,A,C';

DECLARE @Existing TABLE (
    the_id VARCHAR(2),
    SortRank    INT,
    OptionValue INT
);

INSERT INTO @Existing (the_Id, SortRank, OptionValue) VALUES
    ('A', 1, 1),
    ('B', 2, 1),
    ('C', 3, 3),
    ('D', 4, 2),
    ('E', 5, 3)

-- Storing existing values in table variable so we can re-insert records in new order without losing existing OptionValue values
DECLARE @New TABLE (
    id int identity primary key,
    the_id VARCHAR(2),
    SortRank    INT,
    OptionValue INT
);

INSERT INTO @New (the_Id, SortRank, OptionValue)
    SELECT I.the_Id, I.SortRank, E.OptionValue
    FROM (SELECT value AS the_Id,
                 ROW_NUMBER() OVER (ORDER BY CHARINDEX(',' + s.value + ',', ',' + @ids + ',')) AS SortRank
          FROM STRING_SPLIT(@Ids, ',') s
         ) I JOIN
         @Existing E
         ON E.the_Id = I.the_id
    ORDER BY I.SortRank ASC;

SELECT *
FROM @New
ORDER BY id;

这是一个 db<>fiddle。

推荐阅读