我有个问题
如果我有一行看起来像这样
如果我有一行看起来像这样
|ordernumber|qty|articlenumber| | 123125213| 3 |fffff111 |
我怎么能把它分成三行,如下所示：
|ordernumber|qty|articlenumber| | 123125213| 1 |fffff111 | | 123125213| 1 |fffff111 | | 123125213| 1 |fffff111 |
/ J
解决方法
您可以使用递归CTE：
WITH RCTE AS
(
SELECT
ordernumber,qty,articlenumber,qty AS L
FROM Table1
UNION ALL
SELECT
ordernumber,1,L - 1 AS L
FROM RCTE
WHERE L>0
)
SELECT ordernumber,articlenumber
FROM RCTE WHERE qty = 1
编辑：
根据Marek Grzenkowicz的回答和MatBailie的评论,全新的想法：
WITH CTE_Nums AS
(
SELECT MAX(qty) n FROM dbo.Table1
UNION ALL
SELECT n-1 FROM CTE_Nums
WHERE n>1
)
SELECT ordernumber,1 AS qty,articlenumber
FROM dbo.Table1 t1
INNER JOIN CTE_Nums n ON t1.qty >= n.n
