小白学SQL

聚合case实现行转列

--行转列方法一

select deptno,
max(case when job='SALESMAN'then sal end) salesman,
max(case when job='MANAGER' then sal end) manager, 
max(case when job='CLERK' then sal end) clerk from emp
group by deptno

开窗函数

开窗函数格式： 函数名(列)over(partition by 分组列 order by 排序列)

over :关键字表示把函数当成开窗函数而不是聚合函数

partition by子句：可以使用partition by 来进行聚合运算。与group by 不同，partition by 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合运算的，而且不同的 开窗函数所创建的分区也互不影响。

order by 子句：使用order by 子句来指定排序规则，默认是按照升序（asc）排列。

RANK()

例：比如正常排名是1，2，3，4，前3名是并列的名次，结果则是：1，1，1，4。如果 有并列名次的行，会占用下一名次的位置。

DENSE_RANK()

例：比如正常排名是1，2，3，4，前3名是并列的名次，结果则是：1，1，1，2。如果 有并列名次的行，不占用下一名次的位置。

ROW_NUMBER()

例：比如前3名是并列的名次，排名是正常的1，2，3，4。也就是不考虑并列名次 的情况。

select sal,row_number()over(order by sal desc) from emp; 
select sal,dense_rank()over(order by sal desc) from emp; 
select sal,rank()over(order by sal desc) from emp 

--partition 分区/分组 按照部门对工资进行降序排序

select emp.*,row_number()over(partition by deptno order by sal desc) from emp; 
select deptno,sal,dense_rank()over(partition by deptno order by sal desc) from emp; 
select deptno,sal,rank()over(partition by deptno order by sal desc) from emp;

用聚合函数来开窗

聚合窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写 在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列 名。

avg() max() count() sum() min()

可以不加任何条件，如果加order by 则是按照顺序依次进行计算，一般不加order by

--聚合函数搭配开窗

select * from (select emp.*, min(sal)over() m from emp)
select emp.*, max(sal)over (),min(sal)over(),avg(sal)over(),rank()over(order by sal) from emp
select emp.*, sum(sal)over (partition by deptno) from emp--每个部门的工资总和 
select emp.*,Isum(sal)over(order by sal) from emp --累计求和
select emp.*, sum(sal)over(partition by deptno order by sal) from emp--按照部门分组累加

wm_concat函数

wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行 
select wm_concat(ename) from emp;

listagg函数

LISTAGG 将多行合并成一行
--作为普通函数，对工资进行排序，用逗号进行拼接。

select listagg(ename,',')within group(order by sal)name from emp; 
select * from emp order by sal

--作为分组函数：

select deptno,listagg(ename,',')within group(order by sal)name 
from emp group by deptno;

偏移分析函数 （向前向后取值）

lead(params,m,n) 以params为目标向下m 位取数，当取不到时默认为 n,

lag(params,m,n) 以params为目标向上m位取数，当取不到时默认为 n, 在一次查询中取出当前行的同一字段（ params参数）的后面第m行的数据，如 果没有用n代替

--偏移函数

select ename,job,sal,lag(sal,1,0)over(order by sal) lastsal from emp; 
select ename,job,sal,lead(sal,1,0)over(order by sal) lastsal from emp; 

--环比 （sal-lastsal）/sal*100%

select ename,job,sal,lag(sal,1,0)over(order by sal) lastsal,
concat( to_char(round((sal-lag(sal,1,0)over(order by sal))/sal*100,2),'990.99') ,'%') 
from emp;

同比是本年度和去年的相比，可以使用lag(sal,12,0)来实现 如果不是连续月份可以使用序号开窗配合偏移开窗相减判断

子查询、联合查询

单行子查询

单行子查询是指返回一行数据的子查询语句。当where子句引用单行子查询时，可以使用 比较运算符（= 、>、<等）

查询结果表现形式：单行单列 多行单列 多行多列 单行多列

--单行单列，一个值 一个列 一个表

--多行单列 一个列 一个表

select avg(sal) from emp; 

select * from emp where sal>(select avg(sal) from emp);

select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp); 

select ename,sal, (select avg(sal) from emp) from emp;

select pjf from (select avg(sal) pjf from emp)

--多行单列 一个列 一个表

select deptno from dept where deptno>10; 
select * from emp where deptno in (select deptno from dept where deptno>10);
--例子：多条件查询，大多数可以使用开窗代替 

select * from 
(select ename,sal,deptno,rank()over(partition by deptno order by sal desc) r from emp) where r=1;
--查看每个部门工资最高的员工姓名和他的工资开窗的做法 

--多条件多行操作

select ename,sal,deptno from emp
where (deptno,sal) in 
(select deptno,max(sal) from emp group by deptno)

--多行多列 单行多列 一个表

select ename,sal,rank()over(order by sal desc) from emp;
select * from (select ename,sal,rank()over(order by sal desc) paiming from emp) 
where paiming<=3;

练习5

4.实现两种方法的行转列
select deptno,
max(case when job='SALESMAN' then sal end) SALESMAN,
  max(case when job='CLERK' then sal end) CLERK,
    max(case when job='MANAGER' then sal end) MANAGER
      from emp 
      group by deptno
      
select deptno,
max(decode(job,'SALESMAN' , sal)),
  max(decode( job,'CLERK' , sal )),
    max(decode( job,'MANAGER' , sal ))
      from emp 
      group by deptno
      
select * from (select deptno,job,sal from emp) pivot ( max(sal) for job in ('SALESMAN','MANAGER','CLERK') )

练习6

select decode(job,'SALESMAN' , sal) from emp
1.按照部门编号升序查找所有部门名称，用、隔开
select listagg(dname,'、')within group (order by deptno) from dept
2.按照工资降序查找每个部门的员工姓名，用、隔开
select listagg(ename,'、')within group(order by sal desc) from emp
3.使用工资偏移计算环比 （sal-lastsal）/sal*100%
select sal,lag(sal,1,0)over(order by sal),to_char((sal-lag(sal,1,0)over(order by sal))/sal*100,'990.99')||'%' from emp

4.查询员工表中工资最高的前三名
select *  from (select emp.*,row_number()over(order by sal desc) r  from emp) where  r in (1,2,3)

5.查询员工表中每个部门的工资第2~3名的员工信息
select * from 
(select emp.*,row_number()over(partition by deptno order by sal desc) r from emp )where r in(1,2)
6.查询员工姓名、部门及部门平均工资,以及部门内最高工资
select ename,deptno,avg(sal)over(partition by deptno),max(sal)over(partition by deptno) from emp 
7.每种工作累计求工资和
select  sum(sal)over(partition by job order by sal ) from emp

练习7

1.查询工资比20号部门工资都大的员工信息
select * from emp where sal>(select max(sal) from emp where deptno=20 group by deptno ) 
2.查询工资比20号部门工资任意一个大的员工信息
select * from emp where sal>(select min(sal) from emp where deptno=20 group by deptno ) 
3.求最高工资员工的姓名
select ename from emp where sal in ( select max(sal) from emp)
4.查询员工工资和工作都和20号部门同时一样的员工信息
select * from emp where (sal,job) in (select sal,job from emp where deptno=20)
and deptno!=20
5.查询部门平均工资大于整个公司平均工资的部门
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select  avg(sal) from emp) 
6.求工资前五名的员工信息
select  * from 
(select  emp.*,row_number()over(order by sal desc) r from emp)  where r<=5 
7.查询emp表中所有数据的第2条到第5条
select * from
(select emp.*,rownum r from emp) where  r between 2 and 5

8.求每个部门工资最高的员工信息
select emp.* from emp where (deptno,sal) in
(select deptno,max(sal) from emp group by deptno)