select A,replace(wm_concat(B),',','') B from table_name group by A;--好东西 http://blog.csdn.net/gelyon/archive/2010/09/20/5897608.aspx
select A,replace(wm_concat(B),',','') from tb group by A
select a,replace(b,',','') from ( select a,wm_concat(b)over(partition by a order by b) b row_number()over(partition by a order by b desc) rnfrom table) where rn=1; 使用wm_concat的难点在于控制连接的顺序.
如下: SQL> select deptno,wm_concat(empno) from emp group by deptno;
SQL> SQL> select deptno, empno 2 from (select deptno, 3 wm_concat(empno) over(partition by deptno order by empno) empno, 4 row_number() over(partition by deptno order by empno desc) rn 5 from emp) 6 where rn = 1;
--oracle 10及以上
select A,replace(wm_concat(B),',','') B from table_name group by A;这类问题有三种方法,详细参考我的BLOG:http://blog.csdn.net/gelyon/archive/2010/09/20/5897608.aspx
select A,replace(wm_concat(B),',','') B from table_name group by A;--好东西
http://blog.csdn.net/gelyon/archive/2010/09/20/5897608.aspx
select a,wm_concat(b)over(partition by a order by b) b
row_number()over(partition by a order by b desc) rnfrom table)
where rn=1;
使用wm_concat的难点在于控制连接的顺序.
SQL> select deptno,wm_concat(empno) from emp group by deptno;
DEPTNO WM_CONCAT(EMPNO)
------ --------------------------------------------------------------------------------
10 7782,7839,7934
20 7369,7902,7876,7788,7566
30 7499,7698,7654,7844,7900,7521
SQL>
SQL> select deptno, empno
2 from (select deptno,
3 wm_concat(empno) over(partition by deptno order by empno) empno,
4 row_number() over(partition by deptno order by empno desc) rn
5 from emp)
6 where rn = 1;
DEPTNO EMPNO
------ --------------------------------------------------------------------------------
10 7782,7839,7934
20 7369,7566,7788,7876,7902
30 7499,7521,7654,7698,7844,7900
SQL>