17,377
社区成员
发帖
与我相关
我的任务
分享请教大牛: 如何将记录拆分
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
...全文
请发表友善的回复…
发表回复
xumeng929 2011-06-08
- 打赏
- 举报
都是牛人啊..
tangren 2011-06-07
- 打赏
- 举报
狂浪一出手，就知有没有，呵呵~~~~
fbmsyu 2011-06-07
- 打赏
- 举报
#3很好啊。不过 真的不好理解。
304的的哥 2011-06-07
- 打赏
- 举报
--狂狼的解答不是很好么
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
pzgood 2011-06-07
- 打赏
- 举报
昨天发错到mssql里面有人解答得非常好,但是oracle里面缺少一些函数,有没有高手帮忙替换一下.
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
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
小灰狼W 2011-06-07
- 打赏
- 举报
with tmp as
(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
xumeng929 2011-06-07
- 打赏
- 举报
ding........xuexi
304的的哥 2011-06-07
- 打赏
- 举报
--lz是想将price/num作为new price，平分num
--我是这么想的，但是没得出答案
select id,num/num,price/num
from t
connect by level<=num
