我在这张表中有以下数据
Job Quantity Status Repeat 1 100 OK 2 2 400 HOLD 0 3 200 HOLD 1 4 450 OK 3
根据每行的“重复”列中的值,应再次重复该行.例如,对于作业1,重复值为2,因此作业1应重复两次.
结果表应如下所示
Job Quantity Status Repeat 1 100 OK 2 1 100 OK 2 1 100 OK 2 2 400 HOLD 0 3 200 HOLD 1 3 200 HOLD 1 4 450 OK 3 4 450 OK 3 4 450 OK 3 4 450 OK 3
有人可以帮我解决这个问题吗？
我正在使用sql server
解决方法@H_502_16@
这将为任何个人工作(在我的系统上)支持超过7,400次重复.如果需要更多,可以使用其他系统表或交叉连接.
DECLARE @d TABLE (Job INT,Quantity INT,Status VARCHAR(12),Repeat INT);
INSERT @d SELECT 1,100,'OK',2
UNION ALL SELECT 2,400,'HOLD',0
UNION ALL SELECT 3,200,1
UNION ALL SELECT 4,450,3;
WITH x AS
(
SELECT TOP (SELECT MAX(Repeat)+1 FROM @d) rn = ROW_NUMBER()
OVER (ORDER BY [object_id])
FROM sys.all_columns
ORDER BY [object_id]
)
SELECT * FROM x
CROSS JOIN @d AS d
WHERE x.rn <= d.Repeat + 1
ORDER BY Job;
DECLARE @d TABLE (Job INT,Quantity INT,Status VARCHAR(12),Repeat INT); INSERT @d SELECT 1,100,'OK',2 UNION ALL SELECT 2,400,'HOLD',0 UNION ALL SELECT 3,200,1 UNION ALL SELECT 4,450,3; WITH x AS ( SELECT TOP (SELECT MAX(Repeat)+1 FROM @d) rn = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_columns ORDER BY [object_id] ) SELECT * FROM x CROSS JOIN @d AS d WHERE x.rn <= d.Repeat + 1 ORDER BY Job;
