日期:2014-05-17 浏览次数:20585 次
WITH a1 (item_no,qty,price)AS
(
SELECT '001',5,169 UNION all
SELECT '002',3,213
)
,a2 AS
(
SELECT item_no,qty,price,1 n FROM a1
UNION ALL
SELECT item_no,qty,price,n+1
FROM a2
WHERE n<qty
)
SELECT item_no,price FROM a2
ORDER BY item_no
OPTION(MAXRECURSION 0)
create table #ta(name varchar(10),num int,price int)
insert into #ta
select '001',5.0,169
union all select '002',3.0,213
select * from #ta
select a.name,a.price
from
(select a.* ,rn=ROW_NUMBER() over(partition by name order by getdate())
from #ta a,(select top 10 number from master..spt_values where type='P' and number>0)b
)a
inner join #ta b on a.rn<=b.num and a.price=b.price and a.name=b.name
drop table #tb
/*
name price
-----------------
001 169
001 169
001 169
001 169
001 169
002 213
002 213
002 213
*/