当前位置： 移动技术网 > IT编程>数据库>Mysql > mysql 查日期范围内的各个订单量的客户数

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

2020年08月11日  | 移动技术网IT编程  | 我要评论

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网