pivot，unpivot函数的使用

查询每个部门中各个职位的总工资

select deptno , job, sum(sal)
from emp 
group by job, deptno

 

 

使用decode（）函数来行转列

select deptno,
sum(decode(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,
sum(decode(job,'MANAGER',sal,0)) MANAGER_JOB
from emp
group by deptno

 

 

那如果用数据库来行转列，要怎么显示呢

select temp.dno , sum(president_job),sum(manager_job),sum(cleck_job)
from (
select deptno dno,
(select sum(sal) from emp where job='PRESIDENT' AND empno=e.empno) president_job,
(select sum(sal) from emp where job='MANAGER' AND empno=e.empno) manager_job,
(select sum(sal) from emp where job='CLERK' AND empno=e.empno) clerk_job
from emp e ) temp
group by temp.dno
order by temp.dno

 



虽然实现行转列，但是过于复杂

select *
from (select deptno, job , sal from emp)
pivot (
sum(sal)
for job in (
'PRESIDENT' AS president_job ,
'MANAGER' AS manager_job
)
) order by deptno

 



这个函数可以使用any来表示如下：

select *
from (select deptno, job , sal from emp)
pivot XML(
sum(sal)
for job in (ANY)
) order by deptno

 



select *
from (select deptno, job , sal ,
sum(sal) over (partition by deptno) sum_sal,
max(sal) over (partition by deptno) max_sal,
min(sal) over (partition by deptno) min_sal
from emp)
pivot XML(
sum(sal)
for job in (ANY)
) order by deptno

 

统计函数

select *
from (select deptno, job , sal 
from emp)
pivot (
sum(sal) as sum_sal , max(sal) as max_sal, min(sal) as min_sal
for job in (
'PRESIDENT' AS president_job ,
'MANAGER' AS manager_job 
)
) order by deptno

 


这样数据被拆分成一个个显示president_job_sum_sal\president_job_max_sal......

首先执行如下
alter table emp add ( sex varchar2(10) default '男')
update emp set sex='女' where to_char(hiredate,'yyyy') = '1981'
commit

 

select * from
(select deptno, job , sal,sex
from emp)
pivot (
sum(sal) as sum_sal ,max(sal) as max_sal , min(sal) as min_sal
for ( job ,sex) in (
('PRESIDENT','男' )AS president_job ,
( 'MANAGER' ,'女' )AS manager_job
)
) order by deptno

 



unprivot 是列转换之后，保留空

with temp as (
select * from (select deptno, job , sal from emp )
pivot (
sum (sal)
for job in(
'PRESIDENT' AS president_job ,
'MANAGER' AS manager_job 
)
)order by deptno
)select * from temp
unpivot (
sal_sum for job in(
president_job as 'PRESIDENT' ,
manager_job as 'MANAGER'
)
)ORDER BY deptno

 


发现转换之后现在的结果没有显示空，如果要显示空，就用include

with temp as (
select * from (select deptno, job , sal from emp )
pivot (
sum (sal)
for job in(
'PRESIDENT' AS president_job ,
'MANAGER' AS manager_job 
)
)order by deptno
)select * from temp
unpivot include nulls (
sal_sum for job in(
president_job as 'PRESIDENT' ,
manager_job as 'MANAGER'
)
)ORDER BY deptno

 


很明显不带空比较好看。

恢复数据:网上百度查恢复emp表的数据

drop table emp;

CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
emp

 


设置数据层次问题

那什么是层次函数？ 层次查询，一种较为确定数据行之间关系结构的一种操作

select empno,lpad('|-' , LEVEL * 2 , ' ' ) || ename, mgr, LEVEL
from emp 
connect by prior empno=mgr
start with mgr is null

 


这个是从ＫＩＮＧ开始

ＣＯＮＮＥＣＴ＿ＢＹ＿ＩＳＬＥＭ伪列

select empno,lpad('|-' , LEVEL * 2 , ' ' ) || ename, mgr, LEVEL,
decode(connect_by_isleaf,0,'根节点',1,' 叶子节点') isleaf
from emp 
connect by prior empno=mgr
start with mgr is null

 


这个是从ＫＩＮＧ开始

select empno,lpad('|-' , LEVEL * 2 , ' ' ) || ename, mgr, LEVEL,
connect_by_root ename
from emp 
connect by prior empno=mgr
start with mgr is null

 


最大的根节点就是king

指定的雇员信息开始列出， 总king列出

select empno,lpad('|-' , LEVEL * 2 , ' ' ) || ename, mgr, LEVEL,
connect_by_root ename
from emp 
connect by prior empno=mgr
start with empno=7566

 


可以看出empno=7566的根节点

select empno,lpad('|-' , LEVEL * 2 , ' ' ) || sys_connect_by_path(ename, '=>') empname, mgr, LEVEL,
decode(connect_by_isleaf,0,'根节点',1,' 叶子节') isleaf
from emp 
connect by prior empno=mgr 
start with mgr is null

 



order siblings by 字段
在使用层次函数，如果要排列数据，不能直接用order by，而在order by 中间加入一个单词siblings。