一个分组显示的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个逻辑读的效率也要更高。