一个分组显示的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; www.2cto.com
要求显示为这样的格式：
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 www.2cto.com
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 UNI