关于分组常会用到的sql

1、group by 后想把列合并起来，可以用WMSYS.WM_CONCAT(FIELD_NAME)，这在实际写sql中很常用。

2、我们一般在写sql里用group by来分组，有时候要求得到group by的数目时，常常会加上count。这时候如果除了分组的字段，我还要想select其它的字段的话，会报不是分组的字段的错误。而且，我们常常有通过select 出来的内容是根据count(*)排序后的内容，这时候可以用：

OVER(partition by FIELD_NAME1 order by FIELD_NAME2)来进行，且可以根据分组的count的数量进行排序，如：

select *
  from (select tunnel_name 隧道名称,CONTENT 事件内容,STYLE 事件类型,AFF_LEVEL 事件等级,location 位置,PLACE_MILE 地点桩号,BEGIN_TIME 发生时间,CENTER_MILE 隧道中心桩号,TUNNEL_LEN 隧道长度,ROAD_LINE_NAME 所属路线,
                 count(*) OVER(partition by tunnel_name order by tunnel_name) CNT
          from epub.tunnel_rank_temp)
 order by CNTdesc

3、网上摘录的资料:（原文：http://hi.baidu.com/jinliyixi/blog/item/41f180c75cc0fca48326ace4.html）

wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换

 

构建测试表：

Sql代码
create table TABLE1   
(   
   ID   INTEGER,   
  NAME VARCHAR2(10)   
)   
  
create table TABLE2   
(   
   ID   INTEGER,   
   ROLE VARCHAR2(10)   
)   
  
insert into TABLE1 (ID, NAME) values (1, '张三');   
insert into TABLE1 (ID, NAME) values (2, '李四');   
commit;   
  
insert into TABLE2 (ID, ROLE) values (1, '查询');   
insert into TABLE2 (ID, ROLE) values (1, '分析');   
insert into TABLE2 (ID, ROLE) values (1, '决策');   
insert into TABLE2 (ID, ROLE) values (2, '查询');   
commit;  

create table TABLE1
(
ID   INTEGER,
NAME VARCHAR2(10)
)
create table TABLE2
(
ID   INTEGER,
ROLE VARCHAR2(10)
)
insert into TABLE1 (ID, NAME) values (1, '张三');
insert into TABLE1 (ID, NAME) values (2, '李四');
commit;
insert into TABLE2 (ID, ROLE) values (1, '查询');
insert into TABLE2 (ID, ROLE) values (1, '分析');
insert into TABLE2 (ID, ROLE) values (1, '决策');
insert into TABLE2 (ID, ROLE) values (2, '查询');
commit;

要求输出结果：

Sql代码
ID  NAME     ROLE   
1     张三 查询,分析,决策   
2     李四 查询  

ID NAME ROLE
1  张三 查询,分析,决策
2  李四 查询


方法一、使用wmsys.wm_concat

Sql代码
select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id   
group by table1.id,table1.name  

select table1.*,wmsys.wm_concat(role) from table1,table2 where table1.id=table2.id
group by table1.id,table1.name

方法二、使用sys_connect_by_path

Sql代码
select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from   
(select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2  where table1.id =   
  
table2.id)   
start with rn = 1   
connect by prior rn = rn - 1 and prior id = id   
group by id, name  
order by id   
         

select id, name, ltrim(max(sys_connect_by_path(role, ',')), ',') from
(select row_number() over(partition by table1.id order by name) rn,table1.*, role from table1, table2  where table1.id =
table2.id)
start with rn = 1
connect by prior rn = rn - 1 and prior id = id
group by id, name
order by id

方法三、使用自定义函数

Sql代码
create or replace function my_concat(mid in integer) return varchar2       --记住：参数和返回值里的数据类型都不用定义长度   
is  
result varchar2(4000);    --定义变量，记住Oracle中定义变量不需要   
begin  
       for temp_cursor in (select role from table2 where id=mid) loop     --此处在游标FOR循环中使用查询   
            result :=result || temp_cursor.role || ',';    --Oracle中字符连接使用||，而sql server中用+          
       end loop;   
        result := rtrim(result,',');  --去掉最后一个空格,还有Oracle中的赋值前面没有set   
       return result;   
end;   
  
select table1.*,my_concat(table1.id) from table1,table2 where table1.id=table2.id   
group by table1.id,table1.name  
order by table1.id