2个表,
表A:
CGID NAME
1 q
2 w
3 e
4 r
5 t
6 y
7 u
8 i
9 o
10 p表B:
CGID1 SCORE1 CGID2 SCORE2 CGID3 SCORE3
1 1 2 2 3 3
4 4 5 5 6 6
7 7 8 8 9 9通过这两个表联合查询,得到最终的结果如下:CGID NAME SCORE
1,2,3 q,w,e 1,2,3
4,5,6 r,t,y 4,5,6
7,8,9 u,I,o 7,8,9请教大家赐教!
表A:
CGID NAME
1 q
2 w
3 e
4 r
5 t
6 y
7 u
8 i
9 o
10 p表B:
CGID1 SCORE1 CGID2 SCORE2 CGID3 SCORE3
1 1 2 2 3 3
4 4 5 5 6 6
7 7 8 8 9 9通过这两个表联合查询,得到最终的结果如下:CGID NAME SCORE
1,2,3 q,w,e 1,2,3
4,5,6 r,t,y 4,5,6
7,8,9 u,I,o 7,8,9请教大家赐教!
解决方案 »
- oracle内存泄漏在oraagent.bin,oracle bug?
- oracle中怎么查找指定表所涉及的存储过程 急,在线等
- ORACLE8的问题
- 关于sql语句的性能
- 一个比较有挑战性的问题:怎样提高数据插入或更新的速度?
- 从网上下载的Oracle9i简体中文版(三张碟),完成后是一大堆压缩格式的文件,没有一个类似setup之类的文件,如何安装啊!急!!!
- 【急】在存储过程里执行Create Table temp_table as select * from T_Table;为什么出错?
- 急!!高分求救!!!我的C盘已满,怎样将正运行于C区的数据库完全转移到D盘???
- [注意]:Oracle数据库密码可被截获
- 求救oracle中的级联更新问题
- Oracle 判断资料是否存在 再insert
- oracle中如何实现获取上次的更新时间?
10G 可以用WMSYS.WM_CONCAT实现
FROM (SELECT TRUNC ((cgid - 1) / 3) cgid1, wmsys.wm_concat (cgid) cgid,
wmsys.wm_concat (NAME) NAME
FROM a
GROUP BY TRUNC ((cgid - 1) / 3)) a,
b
WHERE a.cgid1 = b.cgid1
2 wmsys.wm_concat(name) name,
3 wmsys.wm_concat(score1) score
4 from (select cgid, name, score1
5 from a,
6 (select cgid1, score1
7 from b
8 union
9 select cgid2, score2
10 from b
11 union
12 select cgid3, score3 from b) b
13 where a.cgid = b.cgid1)
14 group by trunc((cgid + 2) / 3, 0)
15 ;
CGID NAME SCORE
---------- ---------- ----------
1,2,3 q,w,e 1,2,3
4,6,5 r,y,t 4,6,5
7,8,9 u,i,o 7,9,8
select CGID1||','|| CGID2||','||CGID3 as CGID,
NAME1||','|| NAME2||','||NAME3 as NAME,
SCORE1||','|| SCORE2||','||SCORE3 as SCORE
FROM (select B.CGID1,
B.SCORE1,
B.CGID2,
B.SCORE2,
B.CGID3,
B.SCORE3,
max(decode(B.CGID1, A.CGID, A.NAME, '')) NAME1,
max(decode(B.CGID2, A.CGID, A.NAME, '')) NAME2,
max(decode(B.CGID3, A.CGID, A.NAME, '')) NAME3,
from A, B
where A.CGID = B.CGID1(+)) t
SQL> select wmsys.wm_concat(distinct cgid) CGID,
2 wmsys.wm_concat(distinct name) name,
3 wmsys.wm_concat(distinct score1) score
4 from (select cgid, name, score1
5 from a,
6 (select cgid1, score1
7 from b
8 union
9 select cgid2, score2
10 from b
11 union
12 select cgid3, score3 from b) b
13 where a.cgid = b.cgid1)
14 group by trunc((cgid + 2) / 3, 0);
CGID NAME SCORE
---------- ---------- ----------
1,2,3 e,q,w 1,2,3
4,5,6 r,t,y 4,5,6
7,8,9 i,o,u 7,8,9
SQL> select wmsys.wm_concat(distinct cgid) CGID,
2 wmsys.wm_concat( name) name,
3 wmsys.wm_concat(distinct score1) score
4 from (select cgid, name, score1
5 from a,
6 (select cgid1, score1
7 from b
8 union
9 select cgid2, score2
10 from b
11 union
12 select cgid3, score3 from b) b
13 where a.cgid = b.cgid1)
14 group by trunc((cgid + 2) / 3);
CGID NAME SCORE
---------- ---------- ----------
1,2,3 q,w,e 1,2,3
4,5,6 r,y,t 4,5,6
7,8,9 u,i,o 7,8,9