在SQL Server的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?

我有一个插入到SELECT查询。在SELECT语句中，我有一个子查询，其中我想在一个字段中添加一个增量数字。如果我的SELECT查询只返回一条记录，那么该查询将正常工作，但是如果它返回多个行，它将在增量字段中为所有这些行插入相同的数字。有没有办法限制它每次都增加一个数字?

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))
)

1 个解决方案

#1

You can use the row_number() function for this.

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

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)
                             )

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
    . . .
)

#1