select * from tableA 显示结果为:
id num price
1 1 5
2 2 14
3 1 6
4 4 20
要得到下面的查询结果该怎么操作,望指教:
id num price
1 1 5
2 1 7
3 1 7
4 1 6
5 1 5
6 1 5
7 1 5
8 1 5
id num price
1 1 5
2 2 14
3 1 6
4 4 20
要得到下面的查询结果该怎么操作,望指教:
id num price
1 1 5
2 1 7
3 1 7
4 1 6
5 1 5
6 1 5
7 1 5
8 1 5
declare @table table (id int,num int,price int)
insert into @table
select 1,1,5 union all
select 2,2,14 union all
select 3,1,6 union all
select 4,4,20create table #t(id int identity,num int,price int)declare my_cursor cursor
for
select num,price from @table
open my_cursor declare @num int
declare @price int
declare @i int
fetch next from my_cursor into @num ,@price
while(@@fetch_status=0)
begin
set @i=@num
while(@i>=1)
begin
insert into #t select 1,@price/@num
set @i=@i-1
end
fetch next from my_cursor into @num ,@price
end
close my_cursor
deallocate my_cursor
select * from #t
/*
id num price
----------- ----------- -----------
1 1 5
2 1 7
3 1 7
4 1 6
5 1 5
6 1 5
7 1 5
8 1 5
*/
drop table #t
go
create table [tableA]([id] int,[num] int,[price] int)
insert [tableA]
select 1,1,5 union all
select 2,2,14 union all
select 3,1,6 union all
select 4,4,20
-->查询:
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS ID,1 AS NUM,PRICE/NUM AS PRICE
FROM tableA A
JOIN MASTER..SPT_VALUES B ON B.TYPE='P'
AND B.NUMBER BETWEEN 1 AND A.NUM/**
ID NUM PRICE
-------------------- ----------- -----------
1 1 5
2 1 7
3 1 7
4 1 6
5 1 5
6 1 5
7 1 5
8 1 5(8 行受影响)
**/
SQL> with cte as
2 (
3 select rownum as id from dual connect by level <= 100 --这里可以自己改
4 )
5 select row_number() over(order by a.id) as id,1 as num,price/num as price
6 from tablea a join cte b on b.id between 1 and a.num;
ID NUM PRICE
---------- ---------- ----------
1 1 5
2 1 7
3 1 7
4 1 6
5 1 5
6 1 5
7 1 5
8 1 5
8 rows selected