--不知道这个能不能满足楼主的需求 --能力有限只会这个了 select dept,wm_concat(name) from table group by dept;
--想到思路了: WITH tab AS( SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual UNION ALL SELECT 2,'小张','开发中心' FROM dual UNION ALL SELECT 3,'小王','开发中心' FROM dual UNION ALL SELECT 4,'小丽','研发中心' FROM dual ) SELECT decode(grouping(id||name),1,dept,id||name) result FROM tab GROUP BY dept,rollup(id||name);结果: RESULT ---------- 1小明 2小张 3小王 开发中心 4小丽 研发中心
SELECT ID, NAME FROM (SELECT ID, NAME, DEPT FROM TABLE1 UNION ALL SELECT NULL, DEPT, DEPT FROM TABLE1 GROUP BY DEPT) ORDER BY DEPT, ID NULLS LAST
SQL> WITH tb AS( 2 SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual 3 UNION ALL 4 SELECT 2,'小张','开发中心' FROM dual 5 UNION ALL 6 SELECT 3,'小王','开发中心' FROM dual 7 UNION ALL 8 SELECT 4,'小丽','研发中心' FROM dual 9 ) 10 select dt from 11 (select decode(grouping(id||name),1,dept,id||name) dt from tb 12 group by rollup(dept,id||name)) a 13 where dt is not null 14 /
--能力有限只会这个了
select dept,wm_concat(name)
from table
group by dept;
--想到思路了:
WITH tab AS(
SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual
UNION ALL
SELECT 2,'小张','开发中心' FROM dual
UNION ALL
SELECT 3,'小王','开发中心' FROM dual
UNION ALL
SELECT 4,'小丽','研发中心' FROM dual
)
SELECT decode(grouping(id||name),1,dept,id||name) result
FROM tab GROUP BY dept,rollup(id||name);结果:
RESULT
----------
1小明
2小张
3小王
开发中心
4小丽
研发中心
FROM (SELECT ID, NAME, DEPT
FROM TABLE1
UNION ALL
SELECT NULL, DEPT, DEPT FROM TABLE1 GROUP BY DEPT)
ORDER BY DEPT, ID NULLS LAST
2 SELECT 1 id,'小明' NAME,'开发中心' dept FROM dual
3 UNION ALL
4 SELECT 2,'小张','开发中心' FROM dual
5 UNION ALL
6 SELECT 3,'小王','开发中心' FROM dual
7 UNION ALL
8 SELECT 4,'小丽','研发中心' FROM dual
9 )
10 select dt from
11 (select decode(grouping(id||name),1,dept,id||name) dt from tb
12 group by rollup(dept,id||name)) a
13 where dt is not null
14 /
DT
--------------------------------------------
1小明
2小张
3小王
开发中心
4小丽
研发中心
6 rows selected