SQL - 基于计数插入行

Question

我想在表格中根据另一个值设置插入多个重复行 - 欢迎提出想法和建议。

tblType
Type     Qty
Apple    2
Banana   1
Mango    3

tblResult
Apple
Apple
Banana
Mango
Mango
Mango

- BigIWT

Answer 1

你可以使用递归CTE来构建数据：

最初的回答：

你可以使用递归CTE来构建数据：

WITH rcte AS (
    SELECT Type, Qty, 1 AS n
    FROM tbltype
    WHERE Qty > 0

    UNION ALL

    SELECT Type, Qty, n + 1
    FROM rcte
    WHERE n < Qty
)

-- INSERT INTO tblresult
SELECT Type
FROM rcte

- Salman A

Answer 2

您可以使用递归的cte：

with cte as (
   select t.type, t.qty, 1 as start
   from table t
   union all
   select c.type, c.qty, start + 1 
   from cte c
   where start < c.qty
)

insert into table (type)
     select c.type
     from cte c
     order by c.type
     option (maxrecusrion 0);

- Yogesh Sharma

Answer 3

你可以尝试使用递归CTE来解决这个问题

DECLARE @T TABLE
(
    SeqNo INT IDENTITY(1,1),
    [Type] VARCHAR(20),
    Qty INT
)

INSERT INTO @T
VALUES('Apple',2),('Banana',1),('Mango',3)

;WITH CTE
AS
(
    SELECT
        [Type],
        Qty
        FROM @T
    UNION ALL
    SELECT
        [Type],
        Qty = Qty -1
        FROM CTE
            WHERE Qty>1
)
SELECT
    *
    FROM CTE
    ORDER BY [Type]

结果

Type                 Qty
-------------------- -----------
Apple                2
Apple                1
Banana               1
Mango                3
Mango                2
Mango                1

- Jayasurya Satheesh

Answer 4

您可以尝试使用cte和ROW_NUMBER()来根据另一列的值生成行，如下所示。

create table tblType([Type] varchar(20), Qty int)
insert into tblType values
('Apple',    2),
('Banana',   1),
('Mango',   3)

;WITH
  expanded
AS
(
  SELECT [Type], Qty FROM tblType    
  UNION ALL    
  SELECT [Type], Qty - 1 FROM expanded WHERE Qty > 1
)

SELECT
  *,
  ROW_NUMBER() OVER (ORDER BY [Type], Qty) AS unique_ref
FROM
  expanded
ORDER BY
  [Type],
  Qty

输出如下：

Type    Qty    unique_ref
------------------------
Apple    1     1
Apple    2     2
Banana   1     3
Mango    1     4
Mango    2     5
Mango    3     6

- Suraj Kumar

Answer 5

如果您不介意使用系统表 master..[spt_values] 并且在这种情况下您的 Qty 不超过2537，那么您可以使用以下查询。

与递归 CTE 相比，它将更快。

SELECT t1.type 
FROM   tbltype t1 
       INNER JOIN (SELECT ( Row_number() 
                              OVER (ORDER BY (SELECT NULL)) ) RN 
                   FROM   master..[spt_values] T1) t2 
               ON t1.qty >= t2.rn 

- PSK