-- 41 逻辑函数

-- if函数：查询每位员工的工资级别：3000及以上为高，1500-3000为中，1500及以下为低

SELECT ename,sal,if (sal >=3000,"高",if( sal>1500,"中","低")) as 工资级别

FROM EMP;

## 练习 查询每位员工的实发工资(基本工资+提成,没有提成计为0)(用if函数)

SELECT ename,sal,comm,if (comm,sal +comm,sal +0) as 实发工资

FROM EMP;

SELECT ename,sal,comm,if (comm is null,sal +0,sal+comm) as 实发工资

FROM EMP;

-- ifnull函数：查询每位员工的实发工资(基本工资+提成,没有提成计为0):ename,comm,sal,实发工资

SELECT ename,comm,sal,ifnull (comm, 0) +sal as 实发工资

FROM EMP;

-- case when函数 (又称逻辑表达式) case when ...then... else ... end

#工资等级排序,利用字母排序

select ename,sal,

case when sal >= 3000 then "A高"

when sal >1500 and sal <3000 then "B中"

else "C低"

end as 工资级别

from emp

order by 工资级别 ASC;

-- 练习（德邦物流）：计算创建日期在0501-0531期间客户的单量分布情况：单量区间、客户数

-- 课后作业（德邦物流）：计算创建日期在20200501-20200531期间客户的单量分布情况：单量区间、客户数

#单量区间分为四档: '0-5','6-10','11-20' '20以上'

create database wuliu;

use wuliu;

create table 揽收表(

运单号 char(7) primary key,

客户id char(5),

创建日期 date

);

insert into 揽收表 values

('PNO0001','CC001','2020-05-01'),

('PNO0002','CC002','2020-05-01'),

('PNO0003','CC002','2020-05-02'),

('PNO0004','CC003','2020-05-01'),

('PNO0005','CC003','2020-05-02'),

('PNO0006','CC003','2020-05-03'),

('PNO0007','CC004','2020-05-01'),

('PNO0008','CC004','2020-05-01'),

('PNO0009','CC004','2020-05-02'),

('PNO0010','CC004','2020-05-03'),

('PNO0011','CC004','2020-05-04'),

('PNO0012','CC005','2020-05-01'),

('PNO0013','CC005','2020-05-02'),

('PNO0014','CC005','2020-05-02'),

('PNO0015','CC005','2020-05-03'),

('PNO0016','CC005','2020-05-04'),

('PNO0017','CC005','2020-05-05'),

('PNO0018','CC006','2020-05-03'),

('PNO0019','CC006','2020-05-06'),

('PNO0020','CC006','2020-05-07'),

('PNO0021','CC006','2020-05-08'),

('PNO0022','CC006','2020-05-10'),

('PNO0023','CC006','2020-05-11'),

('PNO0024','CC006','2020-05-12'),

('PNO0025','CC006','2020-05-13'),

('PNO0026','CC006','2020-05-15'),

('PNO0027','CC006','2020-05-18'),

('PNO0028','CC006','2020-05-22'),

('PNO0029','CC006','2020-05-25'),

('PNO0030','CC006','2020-06-10');

#问题1,计算不同单量区间的客户数

#思路1,分步计算

#第1步

select 客户id,count(distinct 运单号) as 下单次数,

case when count(distinct 运单号)<=5 then '0-5'

when count(distinct 运单号)<=10 then '6-10'

when count(distinct 运单号)<=20 then '11-20'

else '20以上' end as 单量区间 from 揽收表

where month(创建日期)=5

group by 客户id;

#第2步

select 单量区间,count(客户id) as 客户数

from (select 客户id,count(distinct 运单号) as 下单次数,

case when count(distinct 运单号)<=5 then '0-5'

when count(distinct 运单号)<=10 then '6-10'

when count(distinct 运单号)<=20 then '11-20'

else '20以上' end as 单量区间

from 揽收表

where month(创建日期)=5 group by 客户id)

as t

group by 单量区间;

#思路2,两步合一

select 单量区间,count(客户id) as 客户数

from

(select 客户id,count(distinct 运单号) as 下单次数,

case when count(distinct 运单号)<=5 then '0-5'

when count(distinct 运单号)<=10 then '6-10'

when count(distinct 运单号)<=20 then '11-20'

else '20以上' end as 单量区间 from 揽收表

where month(创建日期)=5 group by 客户id)

as t

group by 单量区间;

#问题2,计算不同单量区间的客户数和占比

select 单量区间,count(客户id) 客户数,count(客户id)/(select count(distinct 客户id) from 揽收表) 占比

from

(select 客户id,

case when count(*) <=5 then '0-5'

when count(*) <=10 then '6-10'

when count(*) <=20 then '11-20'

else '20以上'

end as 单量区间

from 揽收表

where month(创建日期)=5 group by 客户id)

as t1

group by 单量区间;