SQL Server 2008 R2——使用数字辅助表（master..spt_values）实现用计数字段对记录进行重复显示

 1 --by WLS
 2 --20151224
 3 --网络代码有风险复制粘贴须谨慎
 4 USE tempdb
 5 GO
 6 
 7 IF OBJECT_ID (N't_TestbyWLS', N'U') IS NOT NULL
 8 DROP TABLE t_TestbyWLS;
 9 CREATE TABLE t_TestbyWLS(Id INTEGER PRIMARY KEY,Name NVARCHAR(15),Qty INTEGER)
10 GO
11 
12 INSERT INTO t_TestbyWLS VALUES
13 (1,'thbytwo',3),
14 (2,'wlsandwho',2)
15 GO
16 
17 SELECT * FROM t_TestbyWLS
18 GO
19 
20 -------------------------------------------------------------------------------------------
21 WITH TempQty
22 AS
23 (
24     SELECT number AS Num FROM master..spt_values WHERE type='p'
25 )
26 SELECT a.Id,a.Qty FROM t_TestbyWLS a LEFT JOIN TempQty b ON b.Num>0 AND b.Num<=a.Qty
27 GO

 1 -------------------------------------------------------------------------------------------
 2 --step0
 3 SELECT number AS Num FROM master..spt_values WHERE type='p'
 4 GO
 5 --step1
 6 WITH TempQty
 7 AS
 8 (
 9     SELECT number AS Num FROM master..spt_values WHERE type='p'
10 )
11 SELECT a.Id,a.Qty,b.Num FROM t_TestbyWLS a LEFT JOIN TempQty b ON 1=1
12 --step2
13 WITH TempQty
14 AS
15 (
16     SELECT number AS Num FROM master..spt_values WHERE type='p'
17 )
18 SELECT a.Id,a.Qty,b.Num FROM t_TestbyWLS a LEFT JOIN TempQty b ON 1=1 AND b.Num>0 AND b.Num<=a.Qty
19 --step3
20 WITH TempQty
21 AS
22 (
23     SELECT number AS Num FROM master..spt_values WHERE type='p'
24 )
25 SELECT a.Id,a.Qty FROM t_TestbyWLS a LEFT JOIN TempQty b ON b.Num>0 AND b.Num<=a.Qty
26 GO