Oracle+SQL精妙SQL语句讲解.doc

Oracle+SQL精妙SQL语句讲解 Oracle SQL精妙SQL语句讲解 --每月的第一天和最后一天（基于系统时间） select trunc(trunc(sysdate,day)+1,month) dbegin, last_day(trunc(sysdate,day)+1) dend from dual; select trunc(trunc(sysdate,day),month) dbegin, last_day(trunc(sysdate,day)) dend from dual; 构建测试表： 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.i