层次查询、行列转换学习笔记

来源：互联网发布：java 手动编译web项目编辑：程序博客网时间：2024/06/10 05:54

level

语法：

select [level], column, expr...
from table
[where condition]
start with condition
connect by [prior column1= column2 | column1 = prior column2];

举例：按照级别对EMP员工进行排序：

select level, emp.*

from emp

start with ename = 'KING'

connect by prior empno = mgr

order by level;

然后我们再让它显示的更清楚：

select ename,sys_connect_by_path(ename,'/') pename

from emp

start with mgr is null

connect by prior empno=mgr ;

使用connect_by_path(ename,'/')函数

还可以这样，显示更清楚：

select ename,lpad(' ', 2 * level-1)||sys_connect_by_path(ename,'/') "Path"
from emp
start with mgr is null
connect by prior empno=mgr;

分析：

start with 树型结构的起点从哪里开始，而这里我们就从MGR IS NULL这也就是经理是空的人开始。然后connect by prior，这里prior是指优先的意思。这里就需要优先找到KING的员工编号，然后再用MGR=它的员工编写去找下一个。

颠倒一下，我们试着让 mgr=prior empno这样写看一下：

分析：

首先同样是先找到MGR IS NULL的人，这里很明显还是KING，因为只有KING的MGR是空的。然后从这里作为起点开始。再优先去找KING的MGR，而这里就很明显KING的MGR是空的，所以就找不到EMPNO是空的人，然后后面也就没法继续找其他人了。所以这里的结果也只有一行。

不带prior是怎么样的呢？

结果一样：

不写prior的默认值是：
connect by prior mgr=empno;

解析：

select ename,lpad(' ', 2 * level-1)||sys_connect_by_path(ename,'/') "Path"
from emp
start with mgr is null
connect by prior empno=mgr;

lpad是左填充，这里填充的是空格。根据level填充空格数。

函数：列转行函数。很好用

wmsys.wm_concat

col empname for a60
SELECT deptno, wmsys.wm_concat(ename) empname FROM emp GROUP BY deptno;

行转列：

create table score(
    name varchar2(10),
    subject varchar2(10),
    grade number(3)
) ;
insert into score values('Zhang','Language',80);
insert into score values('Zhang','Math',92);
insert into score values('Zhang','English',76);
insert into score values('Li','English',50);
insert into score values('Li','Math',95);
insert into score values('Li','Language',81);
insert into score values('Wang','Language',73);
commit;

21:34:30

select * from score;

用11g的新特性pivot

语法:
SELECT ...
FROM  ...
PIVOT [XML]
   (pivot_clause
    pivot_for_clause
    pivot_in_clause )
WHERE ..

select * from score
pivot (sum(grade)
for subject
in('Language','English','Math'));

也可以这样写：

select name,
sum(decode(subject,'Language', grade,0)) "Language",
sum(decode(subject,'Math', grade,0)) "Math",
sum(decode(subject,'English', grade,0)) "English"
from score
group by name;

列转行：

方法一: 用union all

21:48:48

SELECT name, 'Language' subject,Language grade FROM score2
UNION ALL
SELECT name, 'Math' subject,Math grade FROM score2
UNION ALL
SELECT name, 'English' subject,English grade FROM score2
order by name;

方法二: 用insert all

21:50:35

create table score3(
    name varchar2(10),
    subject varchar2(10),
    grade number(3)
) ;

21:50:53

insert all
into score3 values(name,'Language',Language)
into score3 values(name,'English',English)
into score3 values(name,'Math',Math)
select name,Language,English,Math
from score2;

21:50:58

select * from score3 order by name;

方法三:用11g的新特性unpivot

语法:
SELECT ...
FROM  ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
   (unpivot_clause
    unpivot_for_clause
    unpivot_in_clause )
WHERE ...

21:52:37

Select * from score2
Unpivot
(grade for Subject in(Language,English,Math));

阅读全文

0 0