-- 41 邏輯函數

-- if函數：查詢每位員工的工資級別：3000及以上為高，1500-3000為中，1500及以下為低

SELECT ename,sal,if (sal >=3000,"高",if( sal>1500,"中","低")) as 工資級別

FROM EMP;

## 練習 查詢每位員工的實發(fā)工資(基本工資+提成,沒有提成計為0)(用if函數)

SELECT ename,sal,comm,if (comm,sal +comm,sal +0) as 實發(fā)工資

FROM EMP;

SELECT ename,sal,comm,if (comm is null,sal +0,sal+comm) as 實發(fā)工資

FROM EMP;

-- ifnull函數：查詢每位員工的實發(fā)工資(基本工資+提成,沒有提成計為0):ename,comm,sal,實發(fā)工資

SELECT ename,comm,sal,ifnull (comm, 0) +sal as 實發(fā)工資

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;

-- 練習（德邦物流）：計算創(chuàng)建日期在0501-0531期間客戶的單量分布情況：單量區(qū)間、客戶數

-- 課后作業(yè)（德邦物流）：計算創(chuàng)建日期在20200501-20200531期間客戶的單量分布情況：單量區(qū)間、客戶數

#單量區(qū)間分為四檔: '0-5','6-10','11-20' '20以上'

create database wuliu;

use wuliu;

create table 攬收表(

運單號 char(7) primary key,

客戶id char(5),

創(chuàng)建日期 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,計算不同單量區(qū)間的客戶數

#思路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 單量區(qū)間 from 攬收表

where month(創(chuàng)建日期)=5

group by 客戶id;

#第2步

select 單量區(qū)間,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 單量區(qū)間

from 攬收表

where month(創(chuàng)建日期)=5 group by 客戶id)

as t

group by 單量區(qū)間;

#思路2,兩步合一

select 單量區(qū)間,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 單量區(qū)間 from 攬收表

where month(創(chuàng)建日期)=5 group by 客戶id)

as t

group by 單量區(qū)間;

#問題2,計算不同單量區(qū)間的客戶數和占比

select 單量區(qū)間,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 單量區(qū)間

from 攬收表

where month(創(chuàng)建日期)=5 group by 客戶id)

as t1

group by 單量區(qū)間;