【Mysql面试题】对条件判断函数应用聚合查询

1. 问题描述

已知以下表结构，请用一条SQL语句查询出：安装日期在2018-05-01（含）至2018-05-30（含）期间、且最后登录日期在2018-08-05（含）之后的用户总人数，及在满足前述条件的用户中，2018-08-05（含）之后有过付费的总人数、付费的总次数与付费的总金额。
输出结果包括：用户总人数、付费总人数、付费总次数、付费总金额
表结构：
table_a（每个用户只有一条记录）：uid，install_time（安装日期），last_login_time（最后登录日期）
table_b(日志表，每发生一笔付费产生一条记录)：uid，pay_time，money

2. 数据准备

在navicat中提前打开了一个数据库以应用查询语句

2.1 建表

create table table_a(
	uid int unique,
	install_time datetime,
	last_login_time datetime
);
create table table_b(
	uid int unique,
	pay_time datetime,
	money float
);

2.2 插入数据

insert into table_a values(1,'2018-04-01 00:00:00','2018-04-01 00:00:00');
insert into table_a values(2,'2018-05-01 12:00:00','2018-07-22 00:00:00');
insert into table_a values(3,'2018-05-10 00:00:00','2018-07-24 00:00:00');
insert into table_a values(4,'2018-05-11 00:00:00','2018-08-05 00:00:00');
insert into table_a values(5,'2018-05-25 00:00:00','2018-08-10 00:00:00');
insert into table_a values(6,'2018-05-30 00:00:00','2018-08-11 00:00:00');
insert into table_a values(7,'2018-05-31 00:00:00','2018-06-01 00:00:00');
insert into table_a values(8,'2018-05-31 00:00:00','2018-08-05 00:00:00');
insert into table_a values(9,'2018-05-31 00:00:00','2018-08-10 00:00:00');
insert into table_a values(10,'2018-06-01 00:00:00','2018-08-12 00:00:00');
insert into table_a values(11,'2018-08-01 00:00:00','2018-09-10 00:00:00');

insert into table_b values(1,'2018-09-10 12:20:00',6.66);
insert into table_b values(2,'2018-08-10 12:20:00',18.88);
insert into table_b values(3,'2018-06-25 12:20:00',20.4);
insert into table_b values(4,'2018-07-10 12:20:00',28.8);
insert into table_b values(5,'2018-10-20 12:20:00',30);
insert into table_b values(6,'2018-08-05 00:00:00',42.5);
insert into table_b values(7,'2018-09-10 12:20:00',30.1);
insert into table_b values(8,'2018-12-10 12:20:00',22);
insert into table_b values(9,'2018-06-20 12:20:00',34.5);
insert into table_b values(10,'2018-08-30 12:20:00',99.9);
insert into table_b values(11,'2018-07-29 12:20:00',88.8);

3.查询语句

select 
count(uid) as '用户总人数', 
count(distinct case when pay_time>'2018-08-05 00:00:00' then uid else null end) as '付费总人数', 
count(distinct case when pay_time>'2018-08-05 00:00:00' then pay_time else null end) as '付费总次数',
sum(case when pay_time>'2018-08-05 00:00:00' then money else null end) as '付费总金额'
from (
select a.uid as uid, install_time, last_login_time, pay_time, money
from table_a a left join table_b `b` on a.uid = b.uid
where a.install_time between '2018-05-01 00:00:00' and '2018-05-31 00:00:00'and last_login_time >= '2018-08-05' ) as temp;

4.查询结果