一个分组显示的sql
表内容：
create table TEST2
(
ID NUMBER,
NAME VARCHAR2(20)
)
;
insert into TEST2 (ID, NAME)
values (1, 'stefanie');
insert into TEST2 (ID, NAME)
values (1, 'google');
insert into TEST2 (ID, NAME)
values (1, 'sina');
insert into TEST2 (ID, NAME)
values (2, 'baidu');
insert into TEST2 (ID, NAME)
values (2, 'microsoft');
commit;
要求显示为这样的格式：
ID NAME
---------- ----------------------------------------
1 stefanie,google,sina
2 baidu,microsoft
第一种方法：利用SYS_CONNECT_BY_PATH，具体的解释可以查看http://topic.csdn.net/t/20061220/15/5244199.html
SELECT t.*,
(SELECT ltrim(MAX (SYS_CONNECT_BY_PATH (name, ', ')),',') RESULT
FROM (SELECT id, name, rn,
LEAD (rn) OVER (PARTITION BY id ORDER BY rn) rn1
FROM (SELECT id, name,
ROW_NUMBER () OVER (ORDER BY id,
name DESC) rn
FROM TEST2))
START WITH id = t.id AND rn1 IS NULL
CONNECT BY rn1 = PRIOR rn) VALUE
FROM (SELECT DISTINCT id
FROM TEST2) t
Elapsed: 00:00:00.01
Execution Plan
----------------------
Plan hash value: 1207214063
--------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------
----
| 0 | SELECT STATEMENT | | 2 | 26 | 4 (25)| 00:00:
01 |
| 1 | SORT AGGREGATE | | 1 | 51 | |
|
|* 2 | CONNECT BY WITH FILTERING| | | | |
|
|* 3 | FILTER | | | | |
|
| 4 | COUNT | | | | |
|
| 5 | VIEW | | 5 | 255 | 5 (40)| 00:00:
01 |
| 6 | WINDOW SORT | | 5 | 190 | 5 (40)| 00:00:
01 |
| 7 | VIEW | | 5 | 190 | 4 (25)| 00:00:
01 |
| 8 | WINDOW SORT | | 5 | 50 | 4 (25)| 00:00:
01 |
| 9 | TABLE ACCESS FULL | TEST2 | 5 | 50 | 3 (0)| 00:00:
01 |
|* 10 | HASH JOIN | | | | |
|
| 11 | CONNECT BY PUMP | | | | |
|
| 12 | COUNT | | | | |
|
| 13 | VIEW | | 5 | 255 | 5 (40)| 00:00:
01 |
| 14 | WINDOW SORT | | 5 | 190 | 5 (40)| 00:00:
01 |
| 15 | VIEW | | 5 | 190 | 4 (25)| 00:00:
01 |
| 16 | WINDOW SORT | | 5 | 50 | 4 (25)| 00:00:
01 |
| 17 | TABLE ACCESS FULL | TEST2 | 5 | 50 | 3 (0)| 00:00:
01 |
| 18 | COUNT | | | | |
|
| 19 | VIEW | | 5 | 255 | 5 (40)| 00:00:
01 |
| 20 | WINDOW SORT | | 5 | 190 | 5 (40)| 00:00:
01 |
| 21 | VIEW | | 5 | 190 | 4 (25)| 00:00:
01 |
| 22 | WINDOW SORT | | 5 | 50 | 4 (25)| 00:00:
01 |
| 23 | TABLE ACCESS FULL | TEST2 | 5 | 50 | 3 (0)| 00:00:
01 |
| 24 | VIEW | | 2 | 26 | 4 (25)| 00:00:
01 |
| 25 | HASH UNIQUE | | 2 | 6 | 4 (25)| 00:00:
01 |
| 26 | TABLE ACCESS FULL | TEST2 | 5 | 15 | 3 (0)| 00:00:
01 |
--------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID"=:B1 AND "RN1" IS NULL)
3 - filter("ID"=:B1 AND "RN1" IS NULL)
10 - access("RN1"=NULL)
Statistics
----------------------
1 recursive calls
0 db block gets
56 consistent gets
0 physical reads
0 redo size
541 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
21 sorts (memory)
0 sorts (disk)
2 rows processed
第二种方法：利用wmsys.wm_concat
select id,wmsys.wm_concat(name) name from test2 group by id;
Elapsed: 00:00:00.00
Execution Plan
----------------------
Plan hash value: 3598114683
----------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------
| 0 | SELECT STATEMENT | | 2 | 20 | 4 (25)| 00:00:01 |
| 1 | SORT GROUP BY | | 2 | 20 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST2 | 5 | 50 | 3 (0)| 00:00:01 |
----------------------------------------
Statistics
----------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
535 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2 rows processed
这种方法更好理解：
单用wmsys.wm_concat是这样显示的
SQL> select wmsys.wm_concat(name) name from test2;
NAME
----------------------------------------
stefanie,google,sina,baidu,microsoft
再加上分组group by就得到了需要的结果，而且7个逻辑读比上面的那个56个逻辑读的效率也要更高。