查询：按A分组，满足B时对应的C

1.场景

这种问题我自己归纳为“找对应行”问题，例如有下面一场表（学生做题，对每个知识点的得分情况）

• 字段：主键id、user_id、score、is_study、knowledgeName、updateTime
• 场景1：按用户分组，求某一天用户做了多少题、最高分、最低分对应的知识点名称
• 场景2：按用户分组，求某一天用户最后一次没有学习对应的知识点名称

由此我们可以看出：

• 按A分组：按user_id分组
• 满足B时：最高低分、最近一次
• 对应C的值：对应知识点的名称
  这个C也可以换成其他任何的字段

2.解决思路

（在MySQL8.0之后还可以通过窗口函数来解决）

• 我们可以先写一个子查询：比如最高分就是select user_id , max(score) ... group by user_id
• 然后在外层条件筛选（因为要保证来自A分组，所以用join不用where）

3.MySQL5.7解法

select a.user_id, knowledge_name as good
               from tb as a
                        join (select user_id, max(score) as max
                              from tb
                              group by user_id) as b
                             on a.user_id = b.user_id and a.score = b.max
               group by a.user_id
1
2
3
4
5
6
7

我们在join联表的同时，对A（分组）和B（筛选条件）在on后面进行界定

• 如果不写a.user_id = b.user_id ：那就无法按分区对应
• 如果不写a.score = b.max：那就无法起到筛选作用
• 如果不写group by：每个user_id都会对应很多个值（on后条件有两个，不止是id=id）

因此我们使用了JOIN后跟子查询，而不是where后跟子查询

4.随机取值问题

上面这种SQL有个问题，就是如果有多个并列最高的score，那查出来的顺序是谁？

4.1 null值的排序

默认情况下的升序asc，null值是全部排在最顶行
降序desc，null值全部在最下面
我们可以手动制定MySQL排序null的位置来调整，当然本案例中没影响

4.2 并列score

如果两个数据都是score = 100，则取的是扫描到的第一行

5. MySQL8.0解法

现在场景换成了：user_id、当天的point数量、当天达标的point数量、当天score最高对应的知识点、当天score最低对应的知识点

要同时在查询出来的那一行求出“最大值、最小值”对应的那行数据，按道理我们可以写两次子查询然后join两次（5.7的写法），但在MySQL8.0可以有更简洁的first_value()、last_value()窗口函数（窗口函数详细内容见另一篇《MySQL窗口函数》）

这里直接demo对比下二者在实际场景中的的写法

5. 子查询5.7的写法

很难看

5. 8.0的窗口函数