一道MySQL面试题竟有GroupBy的感悟
面试题
有一张历史评分记录表 t_score_log ，建表语句如下：
create table t_score_log ( `id` varchar(32) not null comment '主键', `appid` varchar(32) not null comment '应用id', `type` tinyint(1) not null comment '评分类型：1运营，2用户', `score` double not null comment '评分', `crtTime` timestamp not null default now() comment '时间', primary key(id) ); -- 插入数据 INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('0cb2-4821-ab68-6267b6f29c07', 'C2003', 2, 32, '2020-06-22 17:39:53'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('2bb6-4875-bc63-ed3df1bfa126', 'C2002', 1, 21, '2020-06-22 17:39:53'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('2bb6-4875-bc63-ed3df222a126', 'C2002', 1, 221, '2020-06-22 19:39:53'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('75e7-422f-bffe-424fad813460', 'C2002', 2, 222, '2020-06-22 17:39:53'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('75e7-422f-bffe-424fad8134b0', 'C2002', 2, 22, '2020-06-22 17:39:53'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('cdeb-4ff8-a198-7ac3e0f1cfb1', 'C2004', 1, 41, '2020-06-22 18:41:41'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('cdeb-4ff8-a198-7ac3e0f1cfb2', 'C2003', 1, 31, '2020-06-22 17:39:53'); INSERT INTO test.t_score_log (id, appid, type, score, crtTime) VALUES ('cdeb-4ff8-a198-7ac3e0f1cfb4', 'C2004', 2, 42, '2020-06-22 18:41:41');
表数据如表格
有很多数据，每个应用都存在两种类型的评分；
请使用一条 SQL 查询，查询结果要求如下显示，查询出每个应用运营和用户最新的评分
SELECT c.appid, (SELECT a.score FROM t_score_log a WHERE a.appid =c.appid AND a.type =1 ORDER BY a.crtTime DESC LIMIT 1 ) AS '运营', (SELECT a.score FROM t_score_log a WHERE a.appid =c.appid AND a.type =2 ORDER BY a.crtTime DESC LIMIT 1 ) AS '用户' FROM t_score_log c GROUP BY c.appid;
感悟
先执行最外层 SQL 语句，GROUP BY 语句是根据后面字段进行拆分成多个表格，然后在执行子查询语句，最后在聚合成一个表格；
select ... from ... where ... group by ... having ... order by;
- 先 from 去查询表
- 根据 where 的条件过滤
- group by 分组
- having 是对分组后的数据过滤
- select 查询
- 据 where 的条件过滤
- group by 分组
- having 是对分组后的数据过滤
- select 查询
- 最后 order by 排序
