-- 41 邏輯函數(shù)

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

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

FROM EMP;

## 練習(xí) 查詢每位員工的實(shí)發(fā)工資(基本工資+提成,沒(méi)有提成計(jì)為0)(用if函數(shù))

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

FROM EMP;

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

FROM EMP;

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

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

FROM EMP;

-- case when函數(shù) (又稱邏輯表達(dá)式) case when ...then... else ... end

#工資等級(jí)排序,利用字母排序

select ename,sal,

case when sal >= 3000 then "A高"

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

else "C低"

end as 工資級(jí)別

from emp

order by 工資級(jí)別 ASC;

-- 練習(xí)（德邦物流）：計(jì)算創(chuàng)建日期在0501-0531期間客戶的單量分布情況：?jiǎn)瘟繀^(qū)間、客戶數(shù)

-- 課后作業(yè)（德邦物流）：計(jì)算創(chuàng)建日期在20200501-20200531期間客戶的單量分布情況：?jiǎn)瘟繀^(qū)間、客戶數(shù)

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

create database wuliu;

use wuliu;

create table 攬收表(

運(yùn)單號(hào) 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');

#問(wèn)題1,計(jì)算不同單量區(qū)間的客戶數(shù)

#思路1,分步計(jì)算

#第1步

select 客戶id,count(distinct 運(yùn)單號(hào)) as 下單次數(shù),

case when count(distinct 運(yùn)單號(hào))<=5 then '0-5'

when count(distinct 運(yùn)單號(hào))<=10 then '6-10'

when count(distinct 運(yùn)單號(hào))<=20 then '11-20'

else '20以上' end as 單量區(qū)間 from 攬收表

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

group by 客戶id;

#第2步

select 單量區(qū)間,count(客戶id) as 客戶數(shù)

from (select 客戶id,count(distinct 運(yùn)單號(hào)) as 下單次數(shù),

case when count(distinct 運(yùn)單號(hào))<=5 then '0-5'

when count(distinct 運(yùn)單號(hào))<=10 then '6-10'

when count(distinct 運(yùn)單號(hào))<=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 客戶數(shù)

from

(select 客戶id,count(distinct 運(yùn)單號(hào)) as 下單次數(shù),

case when count(distinct 運(yùn)單號(hào))<=5 then '0-5'

when count(distinct 運(yùn)單號(hào))<=10 then '6-10'

when count(distinct 運(yùn)單號(hào))<=20 then '11-20'

else '20以上' end as 單量區(qū)間 from 攬收表

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

as t

group by 單量區(qū)間;

#問(wèn)題2,計(jì)算不同單量區(qū)間的客戶數(shù)和占比

select 單量區(qū)間,count(客戶id) 客戶數(shù),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ū)間;