SQL Server 中 INSERT INTO SELECT 查询的字段中添加增量数字 [英] Add an incremental number in a field in INSERT INTO SELECT query in SQL Server

问题描述

我有一个 INSERT INTO SELECT 查询.在 SELECT 语句中，我有一个子查询，我想在其中添加一个字段中的增量数字.如果我的 SELECT 查询仅返回一条记录，则此查询将正常工作，但如果它返回多行，则会在所有这些行的增量字段中插入相同的数字.有没有什么办法限制它每次都加一个增量号?

I have an INSERT INTO SELECT query. In the SELECT statement I have a subquery in which I want to add an incremental number in a field. This query will work fine if my SELECT query and returns only one record, But if it returns multiple rows it inserts the same number in the incremental field for all those rows. Is there any way to restrict it to add an incremental number every time?

INSERT INTO PM_Ingrediants_Arrangements_Temp 
(AdminID,ArrangementID,IngrediantID,Sequence)
(SELECT 
     @AdminID, @ArrangementID, PM_Ingrediants.ID, 
     (SELECT 
          MAX(ISNULL(sequence,0)) + 1 
      FROM
          PM_Ingrediants_Arrangements_Temp 
      WHERE 
          ArrangementID=@ArrangementID)
FROM 
    PM_Ingrediants 
WHERE 
    PM_Ingrediants.ID IN (SELECT 
                              ID 
                          FROM 
                              GetIDsTableFromIDsList(@IngrediantsIDs))
)

推荐答案

您可以为此使用 row_number() 函数.

You can use the row_number() function for this.

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)
    SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,
            row_number() over (order by (select NULL))
    FROM PM_Ingrediants 
    WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)
                             )

如果您想从表中已有的最大值开始，请执行以下操作:

If you want to start with the maximum already in the table then do:

INSERT INTO PM_Ingrediants_Arrangements_Temp(AdminID, ArrangementID, IngrediantID, Sequence)
    SELECT @AdminID, @ArrangementID, PM_Ingrediants.ID,
           coalesce(const.maxs, 0) + row_number() over (order by (select NULL))
    FROM PM_Ingrediants cross join
         (select max(sequence) as maxs from PM_Ingrediants_Arrangement_Temp) const
    WHERE PM_Ingrediants.ID IN (SELECT ID FROM GetIDsTableFromIDsList(@IngrediantsIDs)
                             )

最后，您可以使 sequence 列成为自动递增的标识列.这样就省去了每次都增加它的需要:

Finally, you can just make the sequence column an auto-incrementing identity column. This saves the need to increment it each time:

create table PM_Ingrediants_Arrangement_Temp ( . . .
    sequence int identity(1, 1) -- and might consider making this a primary key too
    . . .
)

这篇关于SQL Server 中 INSERT INTO SELECT 查询的字段中添加增量数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！