本文主要是介绍一道MySQL面试题竟有GroupBy的感悟，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
一道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 查询，查询结果要求如下显示，查询出每个应用运营和用户最新的评分
| appid | 运营 | 用户 |
|---|---|---|
| C2002 | 历史表中该应用运营的最新评分 | 历史表中该应用用户的最新评分 |
| C2003 | ||
| C2004 |
答案
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 排序
这篇关于一道MySQL面试题竟有GroupBy的感悟的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
