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
--lz是想将price/num作为new price,平分num
--我是这么想的,但是没得出答案
select id,num/num,price/num
from t
connect by level<=num
(select 1 num, rownum rn
from (select max(num) x from tablea)
connect by rownum <= x)
select rownum id, t.*
from(
select b.num, a.price / a.num price
from tablea a, tmp b
where a.num >= b.rn
order by a.id, b.rn) t
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
--狂狼的解答不是很好么
SQL> with tablea as(
2 select 1 id,1 num,5 price from dual union all
3 select 2,2,14 from dual union all
4 select 3,1,6 from dual union all
5 select 4,4,20 from dual
6 )
7 , tmp as
8 (select 1 num, rownum rn
9 from (select max(num) x from tablea)
10 connect by rownum <= x)
11 select rownum id, t.*
12 from(
13 select b.num, a.price / a.num price
14 from tablea a, tmp b
15 where a.num >= b.rn
16 order by a.id, b.rn) t
17 /
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