如何在SQL Server插入-选择查询中插入循环值？

Question

我有三张表：

临时保存最后一张正式回执的ORSeries：

|ORSeries|
+--------+
| 1000   |

将保存带有官方收据的用户的Collections：

|   OR   |  ID    |  Date  |
+--------+--------+--------+
|        |        |        |

保存没有正式回执的用户的Users：

|  ID    |  Date      |
+--------+------------+
|  0001  | 08-10-2019 |
|  0002  | 08-10-2019 |
|  0003  | 08-10-2018 |
|  0004  | 08-10-2018 |
|  0005  | 08-10-2018 |

我想给用户开一张官方收据，日期是2018年8月10日。到目前为止，我所拥有的：

INSERT INTO [Collections] (OR, Name, [Date])
    SELECT
        (SELECT SeriesNo + 1 FROM TempORSeries),
        Name,
        [Date]
    FROM 
        ORSeries

预期输出：

|   OR   |  ID    |    Date    |
+--------+--------+------------+
| 10001  |  0003  | 08-10-2018 |
| 10002  |  0004  | 08-10-2018 |
| 10003  |  0005  | 08-10-2018 |

然而，所有用户都收到了相同的官方收据号码。

我的查询返回：

|   OR   |  ID    |    Date    |
+--------+--------+------------+
| 10001  |  0003  | 08-10-2018 |
| 10001  |  0004  | 08-10-2018 |
| 10001  |  0005  | 08-10-2018 |

我该如何解决这个问题呢？

Answer 1

您可以使用row_number + SeriesNo

begin transaction;

declare @SeriesNo int = (case 
                            when not exists(select 1 from Collections) 
                               then (select SeriesNo from TempORSeries)
                               else (select max([OR]) from Collections) 
                         end);

Insert into [Collections] 
    select
        @SeriesNo + (row_number() over (order by (select 1))),
        [Name],
        [Date]
    from
        ORSeries
    where 
        [Date] = '2018-08-10';

commit;

Online Demo With SQL Server 2012 | db<>fiddle

​

​

如何使用上次签发的正式收据更新我的ORSeries表？

这是一个新问题，我建议使用auto increment concat来避免多连接问题。请阅读：

tsql - How do I add string with auto increment value in SQL Server? - Stack Overflow

Answer 2

假设由于某些原因你不能使用，Sql Server2016及更高版本支持。

您应该完全删除ORSeries表，并使用新序列替换它，或者将Collections表中的OR列转换为标识。以下是序列示例：

CREATE SEQUENCE ORSequence START WITH {current sequence value + 1 here};

然后，INSERT语句将如下所示：

Insert into [Collections] 
(
    OR,
    ID, 
    [Date]
)
SELECT
    NEXT VALUE FOR ORSequence,
    ID,
    [Date]
FROM Users WHERE Date = '20181018'

这两种方法都消除了使用事务来控制更新旧ORSeries表的需要-您以前可能没有这样做，因此存在使用相同OR编号的两个会话的风险。

Answer 3

begin transaction

insert Collections
select t1.[OR] + row_number() over(order by t2.ID), t2.ID, t2.Date
from ORSeries t1
cross join Users t2
where t2.Date = "08-10-2019"

update ORSeries
set [OR] = (select MAX([OR]) from Collections where Date = "08-10-2019")

commit transaction