34,837
社区成员




with cte as
(
select 编号,数量-1 as 数量 from T1 where 数量>0
union all
select 编号,数量-1 as 数量 from cte where 数量>0
)
insert T2(编号) select 编号 from cte order by 编号 OPTION(MAXRECURSION 0)
select * from T2
[/quote]
这个可以了,谢谢你。INSERT INTO T2([编号])
SELECT A.编号 FROM T1 A
LEFT JOIN master..SPT_VALUES B ON B.type='P' AND B.number>0 AND A.数量>=B.number
insert into T2
select 编号
from T1 t,master..spt_values s
where s.type = 'P' and s.number >=1 and s.number <= 数量
with cte as
(select 1001 as no,2 as qty union all
select 1002 as no,1 as qty union all
select 1003 as no,4 as qty union all
select 1004 as no,5 as qty ),
cte1 as
(select no, 1 as qty from cte
union all
select a.no,b.qty+1 from cte as a join cte1 as b on a.no=b.no
where a.qty>b.qty)
select no,1 as qty from cte1
order by no
with cte as
(
select 编号,数量-1 as 数量 from T1 where 数量>0
union all
select 编号,数量-1 as 数量 from cte where 数量>0
)
insert T2(编号) select 编号 from cte order by 编号
select * from T2
with cte as
(
select 编号,数量 from T1 where 数量>0
union all
select 编号,数量-1 as 数量 from cte where 数量>0
)
insert T2(编号) select 编号 from cte order by 编号
select * from T2
with cte as
(
select 编号,数量-1 as 数量 from T1 where 数量>0
union all
select 编号,数量-1 as 数量 from cte where 数量>0
)
insert T2(编号) select 编号 from cte order by 编号 OPTION(MAXRECURSION 0)
select * from T2
;with sel (编号 , 数量) as (
select '1001', 2 union all
select '1002', 1 union all
select '1003', 4 union all
select '1004', 5
)
select a.编号 from sel a
join master..spt_values b on b.type='p' and number>0
and b.number<=a.数量