欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-23 14:19:08
查日期在在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 i...

查日期在在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