递增变量foreach记录

Question

我有这样的疑问：

#create table #tmp_table( n_progressive int , name char(10), 
id_numeric(11,0) )


    declare @i int = 0  declare @c int declare n_progressive int = 0


    declare @var_table table ( name char(10), id_number numeric(11,0) )

    insert into @var_table( name, id_number ) select name,id_number from MainTable

    select @c= count (*) from @var_table

    while(@i<@c) begin set @n_progressive = @n_progressive + 1

    insert into #Tmptable( n_progressive  , name , id_numeric ) select @n_progressive ,name,id_numeric from @var_table

    end

var_table中的记录是4。对于每条记录，我希望n_progressive递增+1。

以上查询的结果如下：

+--------------+----------+------------+
|n_progressive | name     | numeric_id |
+--------------+----------+------------+
|1             |   RM1    |   1        |
|1             |   RM2    |   2        |
|1             |   RM3    |   3        |
|1             |   RM4    |   4        | 
|2             |   RM1    |   1        |
|2             |   RM2    |   2        |
|2             |   RM3    |   3        |
|2             |   RM4    |   4        |
|3             |   RM1    |   1        |
|3             |   RM2    |   2        |
|3             |   RM3    |   3        |
|3             |   RM4    |   4        |     
|4             |   RM1    |   1        |
|4             |   RM2    |   2        |
|4             |   RM3    |   3        |
|4             |   RM4    |   4        |    
+--------------+----------+------------+

我想要的是：

+---------------+----------+-------------+
|n_progressive  | name     | numeric_id  |
+---------------+----------+-------------+
|1              |   RM1    |   1         |
|2              |   RM2    |   2         |
|3              |   RM3    |   3         |
|4              |   RM4    |   4         | 
+---------------+----------+-------------+

我不想使用游标。

Answer 1

在循环的每一次迭代中，您都会从@var_table中选择所有记录，这就是为什么您会得到所有记录的4倍(@var_table中的记录数)。

但是，您根本不需要循环，而且在使用SQL的任何时候都应该努力避免循环，因为SQL最适合使用基于集合的方法，而不是过程化方法(有关更多信息，请阅读RBAR: ‘Row By Agonizing Row’和What is RBAR? How can I avoid it?)。

您可以简单地使用row_number()窗口函数来获取n_progressive值，而不是循环：

insert into #Tmptable( n_progressive, name, id_numeric) 
select row_number() over(order by name), name, id_numeric 
from @var_table

Answer 2

您并没有将插入限制为从源表中读取一行，而是多次复制整个表。要直接修复你正在尝试做的事情，你应该这样做……

while(@i<@c) begin

    set @n_progressive = @n_progressive + 1

    insert into
      #Tmptable( n_progressive  , name , id_numeric )
    select
      @n_progressive, name, id_numeric
    from
      @var_table
    WHERE
      id_number = @i    -- Only one row

    SET @i = @i + 1     -- Move to the next row

end

一个更好的想法是使用ROW_NUMBER()，避免循环和许多其他样板代码的需要。

insert into
  #Tmptable( n_progressive  , name , id_numeric )
select
  ROW_NUMBER() OVER (ORDER BY id_numeric),
  name,
  id_numeric
from
  @var_table

一个更好的想法仍然是使用标识列，并让表进行数字分配。

create table
  #tmp_table(
    n_progressive int       IDENTITY(1,1),
    name          char(10)               , 
    id_           numeric(11,0)
  )

insert into #Tmptable(name , id_numeric )
select name, id_numeric
from MainTable
ORDER BY id_numeric

Answer 3

这是你想要的吗？

with n as (
      select 1 as n
      union all
      select n + 1
      from n
      where n < @n_limit
     )
select n.n, name + cast(n.n as varchar(255)), n.n as numeric_id
from n
option (maxrecursion 0);