PLSQL 分组分析日期函数（去空三剑客）

--去空三剑客

4.coalesce(参1,参2....)      多个参数中第一个不为空的值

select coalesce(null,null,123,null,456)
from dual

--查询提成,经理编号,工资
--以及他们中第一个不为空的值
select comm,mgr,sal,coalesce(comm,mgr,sal)
from emp

去空:coalesce(comm,0)
     
--查询每位员工每个月的总工资
select sal,comm,sal+coalesce(comm,0)
from emp

5.nvl(参1,参2)       参数1位空返回参数2,否则返回参数1

select nvl(null,0)
from dual

--查询每个人的姓名和他们的月薪
select ename,sal+nvl(comm,0)
from emp

6.nvl2(参1,参2,参3)     参1为空返回参数3,否则返回参数2

select nvl2(null,2,3),nvl2(1,2,3)
from dual

--如果经理编号为空则返回大哥,否则返回小弟
select ename,nvl2(mgr,'小弟','大哥')
from emp

7.distinct    去重
书写位置紧跟着select

select distinct deptno,job
from emp

--查询emp表有几个部门
select distinct deptno 
from emp

--查询emp表有几个职位
select distinct job
from emp

--查询emp表有几种部门职位
select distinct deptno,job
from emp

8.case when
(1) case 列 when 列中值1  then 值1
            when 列中值2  then 值2
            ...
            [else  值n]
    end

select deptno,
       case deptno when 10 then '十部门'
                   when 20 then '二十部门'
                   else '三十部门'
       end A
from emp

--查询员工表的职位,和中文职位
select job,case job when 'CLERK' then '职员'
                    when 'SALESMAN' then '销售员'
                    when 'MANAGER' then '经理'
                    when 'PRESIDENT' then '董事长'
                    when 'ANALYST' then '分析员'
           end 中文职位
from emp

--查询员工表的部门,和中文部门
select dname,case dname when 'ACCOUNTING'  then '财务部'
                        when 'RESEARCH'    then '研发部'
                        when 'SALES'       then '销售部'
                        when 'OPERATIONS'  then '营运部'
             end 中文部门
from dept

(2) case when 条件1 then 值1
         when 条件2 then 值2
         ...
         [else 值n]
     end

注意:then后面返回值的类型必须一致

--如果部门编号是10返回十部门,20返回20部门以此类推
select deptno,
       case when deptno=10 then '十部门'
            when deptno=20 then '二十部门'
            else '三十部门'
       end A
from emp

--如果工资大于等于3000,返回一级,大于等于2000返回二级,
--大于等于1000返回三级,否则四级

select sal,case when sal>=3000 then '一级'
                when sal>=2000 then '二级'
                when sal>=1000 then '三级'
                else '四级'
            end 工资等级
from emp

--如果工资小于等于8000,返回一级
--        小于等于4000,返回二级
--        小于等于2000,返回三级
--        小于等于1000,返回四级
--        否则返回啥也不是
--查询每个人的姓名,工资,工资等级

select sal,
       case when sal<=8000 then '一级'
            when sal<=4000 then '二级'
            when sal<=2000 then '三级'
            when sal<=1000 then '四级'
            else '啥也不是'
       end A
from emp

(1)先写小范围
select sal,
       case when sal<=1000 then '四级'
            when sal<=2000 then '三级'
            when sal<=4000 then '二级'
            when sal<=8000 then '一级'
            else '啥也不是'
       end A
from emp

(2)补全范围
select sal,
       case when sal<=8000 and sal>4000 then '一级'
            when sal<=4000 and sal>2000 then '二级'
            when sal<=2000 and sal>1000 then '三级'
            when sal<=1000 then '四级'
            else '啥也不是'
        end A
from emp

--如果是1981年之前入职,返回  他的部门编号    
--如果是1981-1982年入职,返回 他入职的年份
--如果是1982年之后入职,返回  他入职的月份
select case when to_char(hiredate,'yyyy')<1981 then to_char(deptno)
            when to_char(hiredate,'yyyy') between 1981 and 1982 then to_char(hiredate,'yyyy')
            when to_char(hiredate,'yyyy')>1982 then to_char(hiredate,'mm')
       end A
from emp

9.decode(列,列中值1,值1,列中值2,值2...值n)
可以和case when 的第一种语法相互转换

--如果部门编号是10返回十部门,20返回20部门以此类推
select decode(deptno,10,'十部门',20,'二十部门','三十部门')
from emp

聚合函数   (分组函数)
--空值是不参与计算的
1.min(列)      最小值
2.max(列)      最大值
3.avg(列)      平均值
4.sum(列)      求和
5.count(列)    计数

--求emp表的最低工资,最高工资,平均工资,工资总和
select min(sal),max(sal),avg(sal),sum(sal)
from emp

--求emp表的最早入职日期,最大员工编号,平均工资,提成之和,人数
select min(hiredate),max(empno),avg(sal),sum(comm),count(empno)
from emp

--查询emp表有提成的人数和总人数
select count(comm),count(1)
from emp

count计数
count(列):空值是不参与计算的
count(1):求行数

--group by  分组
select 
from
[where]
group by

--求每个部门的最低工资
select deptno,min(sal),max(sal)
from emp
group by deptno

--求每个职位的最高工资,最早入职日期
select job,max(sal),min(sal),min(hiredate),count(1)
from emp
group by job

--求每个部门的最低工资
select deptno,min(sal)
from emp
group by deptno

注意:有group by 的时候,select后面只能加分组的列和聚合函数

--求每个部门每种职位的最早入职日期,最晚入职日期
select deptno,job,min(hiredate),max(hiredate)
from emp
group by deptno,job

--having   分组之后的筛选
having后面只能跟聚合函数和用来分组的列
select
from
[where]
group by
having

--求部门人数多于三个的部门的部门编号
select deptno,count(1)
from emp
group by deptno
having count(1)>3

--查询工资大于2000的员工中,按照职位分组,
--求最早入职日期大于1981-1-1的员工职位
select job,min(hiredate)
from emp
where sal>2000
group by job
having min(hiredate)>date'1981-1-1'

--查询部门编号和每个部门的最高工资
--只显示最高工资大于2900的部门
select deptno,max(sal)
from emp
group by deptno
having max(sal)>2900

--order by   排序              --可以用在任何sql中,不是只能用去聚合函数
select
from
where
group by
having
order by

语法:order by 列 [asc]|desc
asc:升序   不写默认升序
desc:降序  

select *
from emp
order by sal desc

--order by后面可以加多个列,中间用逗号隔开
select *
from emp
order by sal desc,empno asc

注意:order by 可以用数字,表示按照第几列排

select *
from emp
order by 6 desc

--查询部门编号和工资,按照部门编号降序排列,部门相同的按照工资升序排列
select deptno,sal
from  emp
order by deptno desc,sal asc

--求每年入职的人数
select to_char(hiredate,'yyyy'),count(1)
from emp
group by to_char(hiredate,'yyyy')

6.[wmsys.]wm_concat(列)    分组连接字符串,以逗号最为分隔符

select deptno,max(sal),count(1),wm_concat(ename)
from emp
group by deptno

以下内容重中之重
null小结
·空不占存储，空不等于0，也不等于空格，也不全等于''   --空字符串
·对一行值进行计算时,比如单行函数/四则运算,空和任何值运算都是空
·对一列值进行计算时,比如聚合函数/分析函数,空不参与计算
·可以用nvl/nvl2/coalesce处理空值
  nvl(列,0) nvl2(列,列,0) coalesce(列,0)
·排序时 空最大
·在greatest和least中 求最大 空最大 求最小 空最小

·where 和 having 的区别
①where 在 group by 之前,对分组前的数据进行筛选
  having 在 group by 之后,对分组后的数据进行筛选
②where 后不能加聚合函数
  having 后只可以加聚合函数和分组的列
  

--执行顺序
select     --5
from       --1
where      --2
group by   --3
having     --4
order by   --6

执行顺序会影响别名

select ename e
from emp 
where e='SMITH'--×

select deptno d,count(1)
from emp 
group by d ---×

select ename e
from emp
order by e --√

★给你一个年份(比如&a),怎么判断它是平年还是闰年   [板书]
1.全年的天数
--明年年初-今年年初
select 
  case when to_date(&a+1||'-1-1','yyyy-mm-dd')-to_date(&a||'-1-1','yyyy-mm-dd')=365
    then '平年' else '闰年' end 平闰年
from dual

--当年最后一天是当年的第几天
select 
  case when to_char(to_date(&a||'-12-31','yyyy-mm-dd'),'ddd')=365
    then '平年' else '闰年' end 平闰年
from dual
2.2月份的天数
--3月1号-2月1号
select 
  case when to_date(&a||'-3-1','yyyy-mm-dd')-to_date(&a||'-2-1','yyyy-mm-dd')=28
    then '平年' else '闰年' end 平闰年
from dual

--2月最后一天是当月的第几天
select 
  case when to_char(last_day(to_date(&a||'-2-1','yyyy-mm-dd')),'dd')=28 
    then '平年' else '闰年' end 平闰年
from dual
3.定义法
整百的年份可以被400整除或者非整百的年份可以被4整除为闰年,否则平年
select
  case when mod(&a,400)=0 or mod(&a,100)!=0 and mod(&a,4)=0 
      then '闰年' else '平年' end 平闰年
from dual

★给你一个日期hiredate,判断这个日期所在年份是平年还是闰年[练习]
1.全年的天数
--明年年初-今年年初
select 
  case when trunc(add_months(hiredate,12),'yyyy')-trunc(hiredate,'yyyy')=365
    then '平年' else '闰年' end 平闰年
from emp
--今年年末是今年的第几天
select 
  case when to_char(trunc(add_months(hiredate,12),'yyyy')-1,'ddd')=365
      then '平年' else '闰年' end 平闰年
from emp
2.2月份天数
--3月1号-2月1号
select 
  case when add_months(trunc(hiredate,'yyyy'),2)-add_months(trunc(hiredate,'yyyy'),1)=28
      then '平年' else '闰年' end 平闰年
from emp
--提取2月最后一天的天数
select
  case when to_char(last_day(add_months(trunc(hiredate,'yyyy'),1)),'dd')=28
      then '平年' else '闰年' end 平闰年
from emp

3.定义法
to_char(hiredate,'yyyy')
同上,略.

小结:
日期函数┌sysdate                          当前系统时间
        ├add_months(日期,数)              日期加减月数
        ├months_between(日1,日2)          日期相差的月数
        ├next_day(日期,'星期几'|数字)     下一个星期几
        ├last_day(日期)                   求日期的月末
        ├round(日期,'格式')               返回最近的日期
        └trunc(日期,'格式')               返回日期之初

通用函数┌userenv('language')              查看客户端字符集
        ├greatest(参1,参2..)              返回多个参数中的最大值
        ├least(参1,参2..)                 返回多个参数中的最小值
        ├coalesce(参1,参2..)              返回多个参数中第一个不为空的值
        ├nvl(参1,参2)                     参1为空返回参2,否则返回参1
        ├nvl2(参1,参2,参3)                参1为空返回参3,否则返回参2
        ├distinct                         去重
        ├case when┌case 列 when 列中值1 then 值1 ..[else 值n]  end
        │         └case when 条件1 then 值1 ..[else 值n] end
        └decode(列,列中值1,值1..值n)      可以和case when的第一种语法相互转换
        
聚合函数┌max   最大值
        ├min   最小值
        ├sum   求和
        ├avg   平均值
        └count 计数
        
select          --5
from            --1
where           --2
group by        --3
having          --4
order by        --6