create database exercise;

use exercise;


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');


#charlly 编写
select 单量,count(客户id) from
(select 客户id,
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 单量;

-- 1.逻辑函数 case when expr1 then  expr2  [when expr3 then  expr4... else expr] end as 新字段名 2.将整个返回结果（）命名一个新表t,作为select中一个表
-- 计算创建日期在0501-0531期间客户的单量分布情况


#老师答案
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 单量;