一道MySQL面试题竟有GroupBy的感悟

本文主要是介绍一道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的感悟的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---