mysql 查日期范围内的各个订单量的客户数

查日期在在5.13-5.18时，订单量1-2，2-3，3-5的客户数

一个在接单时看到的题目

下面是数据库脚本

create table flash(S varchar(10),id varchar(10),day date NOT NULL); insert into flash values('PN0011' , 'cc001' , '2020-05-12'); insert into flash values('PN0012' , 'cc001' , '2020-05-13'); insert into flash values('PN0013' , 'cc001' , '2020-05-14'); insert into flash values('PN0014' , 'cc001' , '2020-05-14'); insert into flash values('PN0015' , 'cc002' , '2020-05-15'); insert into flash values('PN0016' , 'cc002' , '2020-05-16'); insert into flash values('PN0017' , 'cc003' , '2020-05-15'); insert into flash values('PN0018' , 'cc004' , '2020-05-16'); insert into flash values('PN0024' , 'cc004' , '2020-05-16'); insert into flash values('PN0019' , 'cc005' , '2020-05-16'); insert into flash values('PN0020' , 'cc005' , '2020-05-17'); insert into flash values('PN0021' , 'cc005' , '2020-05-17'); insert into flash values('PN0022' , 'cc005' , '2020-05-18'); insert into flash values('PN0023' , 'cc005' , '2020-05-18'); 

解题sql语句

SELECT count(t_1_2.S) `1-2客户数` from (SELECT t.*,count(*) f_count from flash t where t.`day`>"2020-05-13" AND t.`day`<"2020-05-18" GROUP BY t.id HAVING f_count >=1 and f_count<=2 ) t_1_2; SELECT count(t_2_3.S) `2-3客户数` from (SELECT t.*,count(*) f_count from flash t where t.`day`>"2020-05-13" AND t.`day`<"2020-05-18" GROUP BY t.id HAVING f_count >=2 and f_count<=3 ) t_2_3; SELECT count(t_3_5.S) `3-5客户数` from (SELECT t.*,count(*) f_count from flash t where t.`day`>"2020-05-13" AND t.`day`<"2020-05-18" GROUP BY t.id HAVING f_count >=3 and f_count<=5 ) t_3_5; 

本文地址：https://blog.csdn.net/Tomsidi/article/details/107905621