盘点6个SQL小技巧

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 这篇内容介绍了数据库查询中的各种JOIN操作,包括内联接(inner join)、左外联接(left outer join)、右外联接(right outer join)和全联接(full outer join)。其中,LEFT JOIN可以用于替换NOT EXISTS和NOT IN的查询。接着,文章展示了如何查询每个类别中的最高分记录,以及如何利用GROUP BY和LIMIT获取每个类别中的前N个记录。此外,还提到了MySQL 8引入的新语法LATERAL JOIN,用于更方便地处理这类问题。最后,文章提到了如何高效地统计不同时间范围内的数据量以及对比两个表之间的数据差异。

各种join

关于join的介绍,比较流行的就是这张图了,如下:

简单的解释如下:

  • join:内联接,也可写成inner join,取两表关联字段相交的那部分数据。
  • left join:左外联接,也可写成left outer join,取左表数据,若关联不到右表,右表为空。
  • right join:右外联接,也可写成right outer join,取右表数据,若关联不到左表,左表为空。
  • full join:全联接,也可写成full outer join,取左表和右表中所有数据。

但注意上图,里面还有几个Key is null的情况,它可以将两表相交的那部分数据排除掉!

也正是因为这个特性,一种很常见的SQL技巧是,用left join可替换not existsnot in等相关子查询,如下:

sql

复制代码

select * from tableA A 
where not exists (select 1 from tableB B where B.Key=A.Key)

-- 使用left join的等价写法
select * from tableA A 
left join tableB B on B.Key=A.Key where B.Key is null

也比较好理解,只有当左表的数据在右表中不存在时,B.Key is null才成立。

查询各类别最大的那条数据

比如在学籍管理系统中,有一类很常见的需求,查询每学科分数最高的那条数据,有如下几种写法:

sql

复制代码

select * from stu_score s 
where s.course_id in ('Maths','English') 
and s.score = (select max(score) from stu_score s1 where s1.course_id = s.course_id)

比较好理解,考分最高其实就是过滤出分数等于最大分数的记录。

在不能使用子查询的场景下,也可转换成join,如下:

sql

复制代码

select * from stu_score s 
left join stu_score s1 on s1.course_id = s.course_id and s1.score > s.score
where s.course_id in ('Maths','English') and s1.id is null

这和前面用left join改写not exists类似,通过s1.id is null过滤出left join关联条件不满足时的数据,什么情况left join关联条件不满足呢,当s表记录是分数最大的那条记录时,s1.score > s.score条件自然就不成立了,所以它过滤出来的数据,就是学科中分数最大的那条记录。

一直以来,我看到SQL的join的条件大都是a.field=b.field这种形式,导致我以为join只能写等值条件,实际上,join条件和where中一样,支持><likein甚至是exists子查询等条件,大家也一定不要忽视了这一点。

上面场景还有一种写法,就是使用group by先把各学科最大分算出来,然后再关联出相应数据,如下:

sql

复制代码

select * from
(select s.course_id,max(s.score) max_score stu_score s where s.course_id in ('Maths','English') group by s.course_id) sm
join stu_score s1 on s1.course_id = sm.course_id and s1.score=sm.max_score

查询各类别top n数据

比如在学籍管理系统中,查询每学科分数前5的记录,类似这种需求也很常见,比较简单明了的写法如下:

sql

复制代码

select * from stu_score s 
where s.course_id in ('Maths','English') 
and (select count(*) from stu_score s1 where s1.course_id = s.course_id and s1.score > s.score) < 5

很显然,第5名只有4个学生比它分数高,第4名只有3个学生比它分数高,依此类推。

LATERAL join

MySQL8为join提供了一个新的语法LATERAL,使得被关联表B在联接前可以先根据关联表A的字段过滤一下,然后再进行关联。

这个新的语法,可以非常简单的解决上面top n的场景,如下:

sql

复制代码

select * from stu_course c 
join LATERAL (select * from stu_score s where c.course_id = s.course_id order by s.score desc limit 5) s1 on c.course_id = s1.course_id
where c.course_name in ('数学','英语')

如上,每个学科查询出它的前5名记录,然后再关联起来。

统计多个数量

使用count(*)可以统计数量,但有些场景想统计多个数量,如统计1天内单量、1周内单量、1月内单量。

count(*)的话,需要扫描3次表,如下:

sql

复制代码

select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 DAY)
union all
select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 WEEK)
union all
select count(*) from order where add_time > DATE_SUB(now(), INTERVAL 1 MONTH)

其实扫描一次表也可以实现,用sum来代替count即可,如下:

sql

复制代码

select sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 DAY)), 1, 0) day_order_cnt,
sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 WEEK)), 1, 0) week_order_cnt,
sum(IF(add_time > DATE_SUB(now(), INTERVAL 1 MONTH)), 1, 0) month_order_cnt
from order where add_time > DATE_SUB(now(), INTERVAL 1 MONTH)

IF是mysql的逻辑判断函数,当其第一个参数为true时,返回第二个参数值,即1,否则返回第三个参数值0,然后再使用sum加起来,就是各条件为true的数量了。

数据对比

有时,我们需要对比两个表的数据是否一致,最简单的方法,就是在两边查询出结果集,然后逐行逐字段对比。

但是这样对比的效率比较低下,因为它要两个表的数据全都查出来,其实我们不一定非要都查出来,只要计算出一个hash值,然后对比hash值即可,如下:

sql

复制代码

select BIT_XOR(CRC32(CONCAT(ifnull(column1,''),ifnull(column2,'')))) as checksum 
from table_name where add_time > '2020-02-20' and add_time < '2020-02-21';  

先使用CONCAT将要对比的列连接起来,然后使用CRC32或MD5计算hash值,最后使用聚合函数BIT_XOR将多行hash值异或合并为一个hash值。

这个查询最终只会返回1条hash值,查询数据量大大减少了,数据对比效率就上去了。


转载来源:https://juejin.cn/post/7245564421316788284

相关文章
|
1月前
|
SQL 数据库
小技巧:如何让 ABAP OPEN SQL 代码具有自解释性(Self-Explained)
小技巧:如何让 ABAP OPEN SQL 代码具有自解释性(Self-Explained)
|
11月前
|
SQL 缓存 druid
JAVA代码优化,接口优化,SQL优化 (小技巧)(一)
JAVA代码优化,接口优化,SQL优化 (小技巧)(一)
206 0
|
11月前
|
SQL 存储 算法
JAVA代码优化,接口优化,SQL优化 (小技巧)(七)
JAVA代码优化,接口优化,SQL优化 (小技巧)(七)
191 0
|
11月前
|
SQL Java 数据库
JAVA代码优化,接口优化,SQL优化 (小技巧)(六)
JAVA代码优化,接口优化,SQL优化 (小技巧)(六)
115 0
|
11月前
|
SQL 缓存 监控
JAVA代码优化,接口优化,SQL优化 (小技巧)(五)
JAVA代码优化,接口优化,SQL优化 (小技巧)(五)
105 0
|
11月前
|
SQL 缓存 NoSQL
JAVA代码优化,接口优化,SQL优化 (小技巧)(四)
JAVA代码优化,接口优化,SQL优化 (小技巧)(四)
193 0
|
11月前
|
SQL 存储 监控
JAVA代码优化,接口优化,SQL优化 (小技巧)(三)
JAVA代码优化,接口优化,SQL优化 (小技巧)(三)
113 0
|
11月前
|
SQL 设计模式 缓存
JAVA代码优化,接口优化,SQL优化 (小技巧)(二)
JAVA代码优化,接口优化,SQL优化 (小技巧)(二)
161 0
|
SQL Oracle 关系型数据库
【SQL Server】数据处理小技巧, 合理取舍小数位
【SQL Server】数据处理小技巧, 合理取舍小数位
172 0
【SQL Server】数据处理小技巧, 合理取舍小数位