sql——对于行列转换相关的操作

一、lead、lag 函数

二、wm_concat 函数

三、pivot 函数

四、判断函数

遇到需要进行行列转换的数据处理需求，以 oracle 自带的表作为例子复习一下：

一、lead、lag 函数

需要行列转换的表：

select deptno,count(empno) emp_num from emp group by deptno

先将转化结果贴出来易于理解

select emp_num dept1,
     lead(emp_num,1) over(order by e.deptno) dept2,  # 根据 emp_num 得到后1行数据
     lead(emp_num,2) over(order by e.deptno) dept3   # 根据 emp_num 得到后2行数据
     from (select deptno,count(empno) emp_num
           from emp group by deptno) e

得到：

可以看出使用位移函数后，在第二列得到的是第二行往后的数据，第三列得到的是第三行开开始的数据，以此类推，第N列第一行就是第一列的第N行了，顺序也一样，此时只需要得到第一行，就是原来的第一列数据了

select dept1 "部门10",dept2 "部门20",dept3 "部门30"
from (select emp_num dept1,
     lead(emp_num,1) over(order by e.deptno) dept2,
     lead(emp_num,2) over(order by e.deptno) dept3
     from (select deptno,count(empno) emp_num
           from emp group by deptno) e)
where rownum=1;

rownum是oracle的伪列，会在原表中新增一个新的列，来记录每一行的数据（用来表示序号），只能使用 < 或 <=（除=1以外）

实际中可能可以使用for循环处理较大量的数据

二、wm_concat 函数

该函数可以将目标字段的值用逗号连成一行，一般结合 group by 一起使用，查看每个类别的明细，仍使用如下表：

select deptno,count(*) 人数,wm_concat(ename) 姓名
from emp 
Group by deptno;

根据部门分组统计人数，列出每组的 ename (姓名)

三、pivot 函数

格式：select * from 表 pivot(聚合函数 for 分组的列 in (显示分解后的列名1,列名2,…));

select deptno,empno from emp; --员工编号

select * 
from (select deptno,empno from emp) --由上表选择
pivot(count(empno) for deptno in    --部门分组重复计数
('10' as 部门10,'20' as 部门20,'30' as 部门30))

这里的 '10' 为值，后接 as+列名，即所有值为'10'的统计完记为"部门10 "

四、判断函数

select deptno,empno from emp; --员工编号

（一）判断，得到要统计的员工号，不满足的为空

select case when deptno=10 then empno end as "10",
case when deptno=20 then empno end as "20",
case when deptno=30 then empno end as "30"
from emp;

（二）统计，count+列名会统计过滤空值，统计非空值

select count(case when deptno=10 then empno end )as "10",
count(case when deptno=20 then empno end )as "20",
count(case when deptno=30 then empno end )as "30"
from emp;