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

发布于: 2020 年 07 月 10 日
还在划水?这个SQL你能写出来吗?

磕了一个季度的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 from1 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

在快速变化的技术中寻找不变,才是一个技术人的核心竞争力。知行合一,理论结合实践

发布于: 2020 年 07 月 10 日 阅读数: 13
用户头像

书旅

关注

还未添加个人签名 2019.04.11 加入

还未添加个人简介

评论

发布
暂无评论
还在划水?这个SQL你能写出来吗?