根据列值重复行N次

Question

我有下面这张表。

Table A:
ID         ProductFK         Quantity       Price
------------------------------------------------
10         1                  2           100
11         2                  3           150
12         1                  1           120
----------------------------------------------

我需要根据“数量”列的值选择重复行N次。

因此，我需要以下选择结果：

ID        ProductFK         Quantity        Price
------------------------------------------------
10        1                   1          100
10        1                   1          100
11        2                   1          150
11        2                   1          150
11        2                   1          150
12        1                   1          120

- franchesco totti

Answer 1

您可以使用简单的JOIN来获得以下所需结果：

SELECT  t1.*, t2.number + 1 RepeatNumber
FROM    TableA t1
JOIN    master.dbo.spt_values t2 ON t2.type = 'P' AND t2.number < t1.Quantity

以上查询会根据 Quantity 列中指定的数量重复每条记录。

master.dbo.spt_values on type = 'P' 注意事项：
该表用于获取一系列以条件 type = 'P' 硬编码在其中的数字。

- Siyavash Hamdi

---

运行良好，简单明了。 小修改 master.dbo.spt_values t2 on t2.type = 'P' and t2.number < t1.Quantity - IncreMan

这里的问题是它只适用于少于2048条记录。 :( - Barsham

@Barsham，你说得对。但这只是针对小领域的简单答案。如果你需要超过2048条记录，你必须使用其他方法，比如Recursive CTE或者使用master.dbo.spt_values进行CROSS JOIN以获取更大的值。 - Siyavash Hamdi

感谢@SiyavashHamdi的评论。我最终使用了一个已经存在于我的系统中、有100万个连续记录的表进行JOIN操作，性能更符合我的要求。 - Barsham

Answer 2

您可以使用递归CTE和UNION ALL来实现：

;WITH cte AS
  (
    SELECT * FROM Table1

    UNION ALL

    SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
    FROM cte INNER JOIN Table1 t
      ON cte.[ID] = t.[ID]
    WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1

这里是一个可用的SQLFiddle。

这是关于这种技术更详细的解释。

---

由于您的输入对于此递归而言过大，您可以使用辅助表来拥有“许多”虚拟行，然后对每个输入行（CROSS APPLY）使用 SELECT TOP([Order])：

;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
      E02(N) AS (SELECT 1 FROM E00 a, E00 b),
      E04(N) AS (SELECT 1 FROM E02 a, E02 b),
      E08(N) AS (SELECT 1 FROM E04 a, E04 b),
      E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
  SELECT TOP(t.[Order]) N
  FROM E16) ca
ORDER BY 1

(The辅助表是从这里借来的，它允许每个输入行最多有65536行，并且如果需要可以扩展)

(这里是一个可工作的SQLFiddle。)

- Amit

---

你有什么最大的[订单]值？ - Amit

可以使用Cross Apply实现吗？ - franchesco totti

@franchescototti：使用option maxrecursion。 - cha

@cha - 在这里 maxrecursion 是无济于事的。递归在第一个解决方案不再需要时结束。将其限制在任何下限都无法产生所需的输出。 - Amit

@cha - 哦...你是指 MAXRECURSION 0...是的，那是一个有效的选项。 - Amit

Answer 3

generate_series 关键字可以被使用：

Select ID,ProductFK,1 as Quantity, Price from TableA t cross join generate_series(1,t.Quantity);

- Soutik Kumar

---

根据目前的写法，你的回答不够清晰。请编辑以添加更多细节，帮助其他人理解这如何回答所提出的问题。你可以在帮助中心找到关于如何撰写好回答的更多信息。 - Community

Answer 4

CREATE TAblE #temp
(
T_Name      VARCHAR(50),
T_Times      BIGINT
)

INSERT INTO #temp(T_Name,T_Times) VALUES ('ASHISH',4)
INSERT INTO #temp(T_Name,T_Times) VALUES ('PANKAJ',3)
INSERT INTO #temp(T_Name,T_Times) VALUES ('RUPESH',2)
INSERT INTO #temp(T_Name,T_Times) VALUES ('MANISH',5)

SELECT t.T_Name ,t.T_Times FROM
(SELECT  T_Name,T_Times,CAST(('<val>'+REPLICATE(T_Name+'</val><val>',T_Times-1)
+'</val>') AS XML )AS X FROM #temp)t CROSS APPLY t.X.nodes('/val')y(z)

drop table #temp

- Ashish Srivastava

---

请保持你的回答独立完整！频繁引用你的博客网站接近于垃圾信息，并不被认为在Stack Overflow上有用。 - user0042