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

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

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页