树型结构SQL语句.doc

1，select level,sys_connect_by_path(child,/) pathfrom hierstart with parent is nullconnect by prior child = parent;LEVEL PATH - -1 /Asia2 /Asia/China3 /Asia/China/Beijing2 /Asia/Japan3 /Asia/Japan/Osaka3 /Asia/Japan/Tokyoselect level,sys_connect_by_path(a.areaid,/) pathfrom cpcarea astart with a.superid=0connect by prior a.areaid = superid;2，在 Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子：select connect_by_isleaf,sys_connect_by_path(child,/) pathfrom hierstart with parent is nullconnect by prior child = parent;CONNECT_BY_ISLEAF PATH- -0 /Asia0 /Asia/China1 /Asia/China/Beijing3，在Oracle 10g 中还有一个新操作CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子，我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。select connect_by_root child,sys_connect_by_path(child,/) pathfrom hierstart with parent is nullconnect by prior child = parent;CONNECT_BY_ROOT PATH- - Asia /AsiaAsia /Asia/ChinaAsia /Asia/China/BeijingAsia /Asia/JapanAsia /Asia/Japan/OsakaAsia /Asia/Japan/TokyoAustralia /Australia4在Oracle 10g 之前的版本中，如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点），Oracle 就会报出一个错误提示：“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中，只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列CONNECT_BY_ISCYCLE， 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环，那么该行的伪列中就会显示“1”，否则就显示“0”。如下例所示：create table hier2(parent number,child number);insert into hier2 values(null,1);insert into hier2 values(1,2);insert into hier2 values(2,3);insert into hier2 values(3,1);select connect_by_iscycle,sys_connect_by_path(child,/) pathfrom hier2start with parent is nullconnect by nocycle prior child = parent;CONNECT_BY_ISCYCLE PATH- -0 /10 /1/21 /1/2/3 5，oracle 多字段查询数据排重，除了row_number()over(partition by )还能怎么实现2 sum(sal) over (partition by deptno order by ename) 部门连续求和,-各部门的薪水连续求和 3 sum(sal) over (partition by deptno) 部门总和, - 部门统计的总和，同一部门总和不变 4 100*round(sal/sum(sal) over (partition by deptno),4) 部门份额(%), 5 sum(sal) over (order by deptno,ename) 连续求和, -所有部门的薪水连续求和 6 sum(sal) over () 总和, - 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和6，-Create Table temp (num varchar2(15 Char),name varchar2(20 Char),sex varchar2(2 Char),classes varchar2(30 Char),course_name varchar2(50 Char) )-insert into temp(num,name,sex,classes,course_name) values (206211,王艺,男,06-1班,保险学);insert into temp(num,name,sex,classes,course_name) values (206212,肖薇,女,06-2,保险学);insert into temp(num,name,sex,classes,course_name) values (206212,肖薇,女,06-2,财务管理);insert into temp(num,name,sex,classes,course_name) values (206212,肖薇,女,06-2,财务会计);insert into temp(num,name,sex,classes,course_name) values (206213,陈雅诗,女,06-2,电子商务);insert into temp(num,name,sex,classes,course_name) values (206213,陈雅诗,女,06-2,公共经济学);insert into temp(num,name,sex,classes,course_name) values (206213,陈雅诗,女,06-2,公司理财);insert into temp(num,name,sex,classes,course_name) values (206213,陈雅诗,女,06-2,管理学原理);insert into temp(num,name,sex,classes,course_name) values (206213,陈雅诗,女,06-2,保险学);insert into temp(num,name,sex,classes,course_name) values (206214,李丹阳,男,06-1,保险学);insert into temp(num,name,sex,classes,course_name) values (206214,李丹阳,男,06-1,财务管理);insert into temp(num,name,sex,classes,course_name) values (206214,李丹阳,男,06-1,财务会计);insert into temp(num,name,sex,classes,course_name) values (206214,李丹阳,男,06-1,电子商务);insert into temp(num,name,sex,classes,course_name) values (206214,李丹阳,男,06-1,公共经济学);insert into temp(num,name,sex,classes,course_name) values (206215,杨伊琳,女,06-3班,环境管理学);insert into temp(num,name,sex,classes,course_name) values (206215,杨伊琳,女,06-3班,管理学原理);insert into temp(num,name,sex,classes,course_name) values (206215,杨伊琳,女,06-3班,商务谈判);insert into temp(num,name,sex,classes,course_name) values (206216,李佳琪,男,06-2,土地估计);-select * from temp-select max(count(course_name)from tempgroup by num,name,sex,classes-select num,name,sex,classes,course_name,row_number() over(partition by num,name,sex,classes order by course_name) rnfrom temp;-select num,name,sex,classes, max(decode(rn,1,course_name,null) course_name_1, max(decode(rn,2,course_name,null) course_name_2, max(decode(rn,3,course_name,null) course_name_3, max(decode(rn,4,course_name,null) course_name_4, max(decode(rn,5,course_name,null) course_name_5from (select num,name,sex,classes,course_name,row_number() over(partition by num,name,sex,classes order by course_name) rn from temp)group by num,name,sex,classes;-select num,name,sex,classes, (max(decode(rn,1,course_name,null) | max(decode(rn,2, | course_name,null) | max(decode(rn,3, | course_name,null) | max(de