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

最新推荐文章于 2024-02-15 18:05:10 发布

Tomshidi

最新推荐文章于 2024-02-15 18:05:10 发布

阅读量777

点赞数

分类专栏： mysql题库文章标签： sql sql查询 mysql

本文链接：https://blog.csdn.net/tomsidi/article/details/107905621

版权

mysql题库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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