还在划水？这个 SQL 你能写出来吗？

关注
发布于: 2020 年 07 月 10 日
磕了一个季度的MySQL，居然被这道SQL题给搞崩了
﻿
今天朋友在群里发了一个SQL题，我蒙圈了，半天没思路。我磕了整个Q2的MySQL，看各种索引优化、MVCC、锁、B+树，此时心里就只有”花里胡哨，心里没点B树？“
﻿
题目：
有一张表b
字段包括：用户id，年，月，请查询在2020年每个月都有记录的用户id?
﻿
且不讨论有没有什么场景会用到这样的一张表以及其合理性。请用SQL实现上边的问题
﻿
我看到的时候，第一个想到的就是用group by
select user_id from b where year=2020 group by user_id having count(month)=12;
没有建表测试，自己也不确定对不对
﻿
按照平时写业务代码的思维，获取每个月都有哪些用户id，然后取交集，发现走不通啊【手动捂脸】
﻿
那就一步一步来，如果知道一个SQL执行每一步的过程是什么样的，那还会有难写的SQL？
﻿
之前整理过一篇SQL执行原理的文章，有兴趣的可以看一下：SQL查询执行顺序详解
﻿
创建一个测试表，结构如下：
CREATE TABLE `b` (
  `user_id` int(11) NOT NULL,
  `month` int(10) DEFAULT NULL,
  `year` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
﻿
然后造一些测试数据：
insert into b values(1,1,2020),(1,2,2020),(1,3,2020),(1,4,2020),(1,5,2020),(1,6,2020),(1,7,2020),(1,8,2020),(1,9,2020),(1,10,2020),(1,11,2020),(1,12,2020),(1,1,2020);
insert into b values(2,1,2020),(2,2,2020),(2,3,2020),(2,4,2020),(2,5,2020),(2,6,2020),(2,7,2020),(2,8,2020),(2,9,2020),(2,10,2020),(2,11,2020),(2,12,2020);
insert into b values(6,1,2020),(6,2,2020),(6,3,2020),(6,4,2020),(6,5,2020),(6,6,2020),(6,7,2020),(6,8,2020),(6,9,2020),(6,10,2020),(6,11,2020),(6,12,2020);
insert into b values(10,1,2020),(10,2,2020),(10,3,2020),(10,4,2020),(10,5,2020),(10,6,2020),(10,7,2020),(10,8,2020),(10,9,2020),(10,10,2020),(10,11,2020),(10,12,2020);
insert into b values(25,1,2020),(25,2,2020),(25,3,2020),(25,4,2020),(25,5,2020),(25,6,2020),(25,7,2020),(25,8,2020),(25,9,2020),(25,10,2020),(25,11,2020),(25,12,2020);
insert into b values(66,1,2020),(66,2,2020),(66,6,2020),(66,4,2020),(66,5,2020),(66,6,2020),(66,7,2020),(66,8,2020),(66,9,2020),(66,10,2020),(66,11,2020),(66,12,2020);
insert into b values(7,1,2020),(7,2,2020),(7,4,2020),(7,5,2020),(7,7,2020),(7,9,2020),(7,10,2020),(7,11,2020);
insert into b values(12,1,2020),(12,4,2020),(12,5,2020),(12,7,2020),(12,9,2020),(12,10,2020),(12,11,2020);
insert into b values(12,1,2019),(12,4,2019),(12,5,2019),(12,7,2019),(12,9,2019),(12,10,2019),(12,11,2019);
﻿
第一步，先从简单的开始，我就先查出来year为2020的，但是，因为可能会有重复数据，所以我顺便去重
select DISTINCT month,user_id from b where year=2020;
说明：这里发现个坑啊，如果使用DISTINCT，必须得放最前边？我如果把user_id放前边会报错
select user_id,DISTINCT month from b where year=2020;
﻿
﻿
这个SQL的执行结果，聪明的你一定知道是什么样的，因为数据较多，截取部分如下：
﻿
﻿
我们假设将这个查询结果叫表1
﻿
看到这个结果我就在想，那我按照user_id进行分组，然后统计个数不就行了？既然是2020年的每一个月都有记录，那么，如果一个用户在表1中有12条记录，这个用户就是我要找的
select user_id from 表1 as c group by c.user_id having count(month)=12;
﻿
OK，那到这里基本就出来了，表1是个子查询
select user_id from (select DISTINCT month,user_id from b where year=2020) as c group by c.user_id having count(month)=12;
执行结果
﻿
我最开始想到的那个SQL显然是不对的，没有考虑重复的情况
﻿
这里插一个小知识
﻿
为什么不能用select  from table group by id，为什么一定不能是，而是某一个列或者某个列的聚合函数
﻿
这个可能就需要了解group by的执行原理
﻿
假设有一个test表，记录如下：
﻿
记为table1
﻿
执行以下SQL得到table2
SELECT name FROM test GROUP BY name;
﻿
﻿
如果对SQL的执行顺序比较熟悉的话，就应该知道，上边这个SQL会先查询出test表中的所有记录之后，然后执行的group by操作（之前整理过一篇SQL的执行原理文章，感兴趣的可以看看：SQL查询执行顺序详解） 
﻿
那么，MySQL在执行group by的时候做了什么？
可以想象MySQL在执行group by的时候，生成了下边这样的一个虚拟表
﻿
右边为执行group by name之后生成的虚拟表，如果此时我们不是select name，而是select *
﻿
显然是会报错的，id和number中有的单元格里面的内容是多个值的，而关系数据库就是基于关系的，单元格中是不允许有多个值的
﻿
再看name列，每个单元格只有一个数据，所以我们select name的话，就没有问题了。为什么name列每个单元格只有一个值呢？因为我们就是用name列来group by的
那么对于id和number里面的单元格有多个数据的情况怎么办呢？答案就是用聚合函数，聚合函数就用来输入多个数据，输出一个数据的。如cout(id),sum(number)，而每个聚合函数的输入就是每一个多数据的单元格
例如我们执行select name,sum(number) from test group by name，那么sum就对虚拟表的number列的每个单元格进行sum操作，例如对name为aa的那一行的number列执行sum操作，即2+3，返回5，最后执行结果如下：
﻿
﻿
group by执行原理参考：
https://blog.csdn.net/qq403580298/article/details/90756352
﻿
在快速变化的技术中寻找不变，才是一个技术人的核心竞争力。知行合一，理论结合实践
﻿
﻿