SQL提高篇：让你的SQL能力飞起来

作为一个软件开发工程师，无论是前端还是后端，大家都知道互联网时代，咱们一个应用程序玩得主要就是数据，最值钱的东西还是数据。尤其作为一名合格的Java开发工程师，说起数据库大家肯定不会陌生。

做过开发的同学都知道，在开发中性能永远不能排在第一位，并不是说性能并不重要，凌驾于性能这个点上的就是数据安全，如果摒弃数据安全过分的追寻性能高效，那显然是一种很不切实际甚至愚蠢至极的行为。咱们只能在保证应用数据安全的前提之上，才能尽可能的优化咱们的查询性能，比如加入Redis缓存，使用ElasticSearch，甚至使用消息队列保证数据最终一致性进而提高并发效率。

今天呢，咱们不谈什么高大上的技术，主要讲解一下咱们在工作中，所有后端开发工程师都会遇到的问题，也就是最基础的CRUD，即增删改查。说起增删改查，大家脑子里肯定会想起一大堆的名词，比如从最原始的Jdbc、再到咱们企业目前最流行的Mybatis、SpringDataJpa以及大幅度提高咱们开发效率的Mybatis-Plus等数据持久层框架。但是呢，作为一个数据层，最重要的还是咱们的SQL，所以说呢，提高咱们的SQL能力就显得至关重要，接下来了，给大家讲解几种大家开发中常见的几种SQL业务类型，可以负责的说，学会了这些套路，勤加练习，无论是怎么样的SQL对我们来说都是小菜一碟。

一、常见的几种棘手SQL查询类型

1.1where和having关键字的区别

where和having这两个关键字我相信大家并不陌生，甚至还比较亲切，但是我想说真正理解这两个关键字实质的人少之又少，我相信很多同学都是靠背的，比如where用于分组前筛选，having用于分组后进一步筛选，使用聚合函数必须使用having进行筛选等。

大家之前的理解并没有错，但这并不是这两者的实质差距。那么这两者的区别主要是什么呢？咱们接着往下看：

where和having都能表示对结果集进行筛选，能用where的地方同样可以使用having。比如咱们看下面这张表：

 

这张表为t_emp员工表表，一共有三个字段：empno用于表示员工编号、empname表示员工姓名，salary表示员工工资。比如这时候我们要查询工资大于等于8000的员工信息，这应该很简单，比如咱们SQL语句如下：

select *from t_emp t where t.salary>=8000;

这个很好理解，一点难度都没有，那么咱们这时候将where换成having呢？如下所示：

select *from t_emp t having t.salary>=8000;

这个时候可能有小伙伴会表示质疑了，也可能打破部分小伙伴的一贯认知了，咱们的having其实跟where一样表示对结果集进行筛选，甚至比where的功能更为强大，能用where的地方一定能用having，咱们不妨看下这条语句的执行结果：

 

上面的运行结果也论证了咱们的逻辑是正确的，既然where和having都可以，为什么还会出现两个关键字呢，那么它们两到底有什么区别了？咱们这时候提出一个新的需求，咱们需要统计每个员工的年薪，按照12个月计算，这个时候咱们的SQL语句如下：

select t.empno,t.empname,t.salary,t.salary*12 year_income from t_emp t;

这个时候咱们的运行结果如下：

 

这个时候有个新需求，需要查询出满足年薪大于50000的员工，这个时候有的同学想都没想，就行云流水的写出了如下的SQL，堪称完美。

SELECT

t.empno,

t.empname,

t.salary,

t.salary * 12 year_income

FROM

t_emp t

WHERE

year_income > 50000;

结果一运行发现，执行的结果完全报错，如下图所示：

 

但是咱们试下此时将where换成having呢，语句如下：

SELECT

t.empno,

t.empname,

t.salary,

t.salary * 12 year_income

FROM

t_emp t

HAVING

year_income > 50000;

咱们不妨看看咱们此时的运行结果：

 

这个时候居然不报错，同样的SQL语句只是筛选关键字不一样，执行效果却完全不一样。通过这个例子，是想告诉同学们：where和having都能表示条件筛选，where只能对表中存在的的字段进行筛选，比如我们查询的t_emp表，根本就不存在year_income，它是通过已有字段运算出来的一个虚拟字段，而并非咱们t_emp真实存在的字段，所以用where查询就会报找不到对应字段的错误，而咱们的having和where用法差不多，但是它比where更强大，即使是表里面不存在，通过逻辑计算出的虚拟字段也能进行筛选。我们为了验证where的筛选局限性，如果将这个逻辑必须使用where完成，咱们可以这么改造。

SELECT

p.*

FROM

( SELECT t.empno, t.empname, t.salary, t.salary * 12 year_income FROM t_emp t ) p

WHERE

p.year_income > 50000;

这个时候咱们就不能将t_emp看成一张表了，咱们需要将年薪整体再看做一张表，也就是咱们的p表，这个时候在p表中真实存在year_income字段，使用where即可完成筛选，但是一般使用子查询咱们SQL的执行效率会降低一个优先级，用having的效果会更好。

where和having主要的区别就在于where只能筛选当前查询表中真实存在的字段，having除此之外还能筛选表里面不存在，且通过表里字段计算出的逻辑字段。就像分组后，一般都和聚合函数搭配在一起，这个时候再对聚合函数字段筛选显然只能通过having。比如一个表里的最大值、最小值等肯定是不存在的字段，而是整体计算出的一个字段。

1.2分组搭配聚合函数带来的坑

分组group by语句相信大家都用到过，比如按照部门查询出某个部门的最高工资、平均工资等等。进行分组后咱们查询的维度就变成以分组字段为准了，具体体现是一分组，查询字段只能是group by后面的字段，要想查询其它字段只能使用聚合函数。

比如对这样一张表：

 

这张表表示员工信息表，同样是t_emp表，其中empno表示当前员工的编号、empname表示员工姓名、gender表示性别、degree表示通过对应的绩效分数、mgrno用于表示员工所属领导的编号。

针对这张表，咱们需要查询每个领导下面对应员工的最高绩效分数，这个时候思路也很简单，我们针对这张表，按照mgrno分组即可，如下所示：

SELECT

t.mgrno,

max( t.degree ) max_degree

FROM

t_emp t

WHERE

t.mgrno IS NOT NULL

GROUP BY

t.mgrno;

这种写法很常见，没啥难度，但是这个时候要求显示每个部门最高绩效的员工所有信息呢？这个时候有同学经常犯得错误就来了。不是要求显示所有字段吗？于是就有了这种比较错误的写法：

SELECT

t.mgrno,

max( t.degree ) max_degree,

t.*

FROM

t_emp t

WHERE

t.mgrno IS NOT NULL

GROUP BY

t.mgrno;

这个时候也没做什么大改动，只是将其它的字段通过t.*将其一一列举出来，这个时候咱们看一下执行结果：

 

这个时候有的同学就比较纳闷了，为什么查询出来的结果是错的？一直看自己的语句，也找不出任何的问题。

这个错误主要是对分组没有彻底理解，我们是按照mgrno也就是领导编号进行分组的，这个时候呢，咱们查询的维度也就变成以mgrno为基准了，说通俗一点，mgrno只能出现唯一且不可重复的数据，而最原始的t_emp表中因为领导相对员工是一对多，比如1001下有1003、1004、1005、1006以及1007这5个员工，所以对应5条数据，但是这时候你以领导编号mgrno进行分组，这个时候1001只能对应一条数据，其它的5条员工数据应该匹配哪一条呢，并不是说你后面写了个max(t.degree)就直接给你匹配上了，它默认匹配的是员工第一条，更高的版本直接报错。生活中常见的例子，比如一个班有80个人，我们按照班级为基准统计，我们只能说某某班级有多少人，某某班级成绩最好或者成绩最差的学生。只能从多条子数据中选择一条有代表性的来表示整个班的水平。

对这种业务咱们怎么解决?咱们可以换个角度思考，每个领导下的最大绩效对应的那个员工肯定也是t_emp表中存在的数据，属于t_emp表中的子集。我们既然通过聚合能够查询出每个领导下最高绩效是多少，我们通过这两个字段去t_emp匹配不就可以了吗？

SELECT

t2.*

FROM

(

SELECT

t.mgrno,

max( t.degree ) max_degree

FROM

t_emp t

WHERE

t.mgrno IS NOT NULL

GROUP BY

t.mgrno

) t1

INNER JOIN t_emp t2 ON t1.mgrno = t2.mgrno

AND t1.max_degree = t2.degree;

这是开发中很常见的一种情况，希望同学们一定要掌握，group by分组后，咱们查询的维度就发生了改变，咱们要查询普通字段只能是group by后的字段，其它字段只能是聚合函数的形式。如果还需要查询其它字段，只能根据咱们的业务规则进行连接查询，也是比较常用的一种解决方法。

1.3排名问题

排名问题也是开发中最常见最棘手的一种情况，比如我们还是对上面的表来说，表数据如下：

 

这张表的含义咱们说过，就不再一一描述了，这个时候咱要查询的的规则很简单，就需要查询出绩效最高的员工信息且不包括重复的。

第一种：咱们可以借助limit这个关键字，按照员工绩效字段进行降序，然后截取第一条数据即可。

SELECT

*

FROM

t_emp t

ORDER BY

t.degree DESC

LIMIT 1;

结果如下：

 

可以看出这种解法很巧妙，能够比较简单的完成咱们的需求。将order by排序与截取关键字imit进行搭配往往能取得意想不到的效果。

但是这个时候，如果我们要求将绩效分数最高的全部员工筛选出来了，比如我再插入一条数据：

 

这个时候有的同学说，这还不简单，按照degree降序limit 0,2。但是往往我们并不知道相同的数据有多少个，所以这个时候这种算法肯定就是错误的了。

第二种：这时候咱们可以使用子查询，先筛选出最大的绩效，再进行子查询：

SELECT

*

FROM

t_emp e

WHERE

e.degree = ( SELECT max( t.degree ) FROM t_emp t )

接下来，咱们需求升级，这时候我不需要最大的绩效了，我需要筛选出绩效是第2名的，这个时候我们怎么思考了，mysql只为咱们提供max()函数，可没有提供第二的函数啊。这个时候我们使用max()算出最大的，然后将其排除掉再求最大的不就是第二大的了吗？

SELECT

*

FROM

t_emp ee

WHERE

ee.degree = (

SELECT

max( e.degree )

FROM

t_emp e

WHERE

e.degree <> ( SELECT max( t.degree ) FROM t_emp t )

);

这种解法没有错，但是适用性太局限了，且子查询太多，效率也较低，比如有上万条数据，我要查询排名1000的，这个子查询次数都需要写1000次，max函数调用1000次，想想恶不恶心。

第三种：使用order by配合limit，最后子查询的方式

咱们说了上面一种方式局限性很明显，查询排行第一、第二还行，但是查询的排名越大，子查询嵌套越多，肯定是不行的，这个时候咱们可以使用limit关键字，咱们首先将数据按照degree降序且去重，如下所示：

SELECT DISTINCT

t.degree

FROM

t_emp t

ORDER BY

t.degree DESC;

下一步比如我们查询第四名的，则limit3,1即可获取到第四名员工所对应的绩效分数。

SELECT DISTINCT

t.degree

FROM

t_emp t

ORDER BY

t.degree DESC

LIMIT 3,1;

最后一步子查询，从t_emp筛选出和第四名分数对应的员工：

SELECT

e.*

FROM

t_emp e

WHERE

e.degree = ( SELECT DISTINCT t.degree FROM t_emp t ORDER BY t.degree DESC LIMIT 3, 1 );

执行结果如下：

 

第四种：第三种看起来没啥问题，但是也存在一定的局限性，比如我们要查询员工表中绩效在第1名、第5名以及第8名的员工了，显然就有点太力不从心，虽然我们也可以通过第三种实现，有多个排名使用union all取并集：

SELECT

e.*

FROM

t_emp e

WHERE

e.degree = ( SELECT DISTINCT t.degree FROM t_emp t ORDER BY t.degree DESC LIMIT 0, 1 )

UNION ALL

SELECT

e.*

FROM

t_emp e

WHERE

e.degree = ( SELECT DISTINCT t.degree FROM t_emp t ORDER BY t.degree DESC LIMIT 4, 1 )

UNION ALL

SELECT

e.*

FROM

t_emp e

WHERE

e.degree = ( SELECT DISTINCT t.degree FROM t_emp t ORDER BY t.degree DESC LIMIT 7, 1 );

但是这个时候要是排名的名次较多，比如20个排名，我们可以发现union all就会急剧暴增。

第五种：使用连接查询。

我们可以转化一下思想，既然是求排名，比如第三名是怎么算的吗，我们是不是对于员工来说，如果它是第三名，那么就一定存在两条比它大的信息，同理第7名，就一定存在6条比它大的数据。

有了这个思想，我们就可以使用连接查询的思想，将t_emp关联t_emp连接条件为第一个表的degree小于第二个表的degree，比如对122这个分值而言，没有比它大的数据，副表的数据条数就为0。对120这个分数而言，比它大的有2条122，我们将其去重，也就一个122比它大。具体语句如下：

SELECT

*

FROM

t_emp e1

LEFT JOIN ( SELECT DISTINCT t.degree FROM t_emp t ) e2

ON e1.degree < e2.degree

ORDER BY

e1.degree DESC;

运行结果如下：

 

此时我们可以通过副表的数据条数来统计每个员工的排名，比如对于97分来说，有楚雅柔和殷梨亭两个人，关联的副表数据分别有3条，也就是比97分大于的分数有三个，那么97这个分数也就是排在第4名，也就是说楚雅柔和殷梨亭这两个人并列第四名。

有了这个思想后，也就比较简单了，我们只需要按照第一个表字段分组，统计第二表的数据条数即可。

SELECT

e1.empno,

e1.empname,

e1.gender,

e1.degree,

e1.mgrno,

count( e2.degree ) + 1 rk

FROM

t_emp e1

LEFT JOIN ( SELECT DISTINCT t.degree FROM t_emp t ) e2 ON

e1.degree < e2.degree

GROUP BY

e1.empno,

e1.empname,

e1.gender,

e1.degree,

e1.mgrno

ORDER BY

e1.degree DESC;

执行结果如下所示：

 

接下来就比较简单了，我们有了rk这个序号字段后，通过条件查询即可，追钟完整的SQL语句如下所示：

SELECT

e1.empno,

e1.empname,

e1.gender,

e1.degree,

e1.mgrno

FROM

t_emp e1

LEFT JOIN ( SELECT DISTINCT t.degree FROM t_emp t ) e2 ON

e1.degree < e2.degree

GROUP BY

e1.empno,

e1.empname,

e1.gender,

e1.degree,

e1.mgrno

HAVING

count( e2.degree ) + 1 in(1,5,8)

ORDER BY

e1.degree DESC;

执行结果如下：

 

1.4统计问题

最后一种，咱们讲一下开发中也出现过很多次的问题，那就是统计，这个我们用一个例子说，有下面一个表t_users，如下图所示：

 

t_users表中，主要记录每个用户的消费收入情况，其中id为用户编号，name表示姓名，earn表示挣得钱，cost表示花费的钱，date表示对应的日期。例如第一条数据表示周芷若在2022-10-01这一天挣了500元，花了200元。第四条表示周芷若在2022-10-02这一天挣了800元，花了600元。

这个时候我们的需求是统计每个用户在每一天手里存的钱为多少，比如对周芷若来说2022-10-01对应的余额为500-200=300，2022-10-02所对应的余额为800-600+300=500，2022-10-03所对应的余额为900-900+500=500。

那么这道题这么解决？我们可以抽象出模型，一个用户每天存得钱为earn-cost，但是还需要加上之前的余额，才是这一天手里真正的余额，我们同样可以使用连接查询的思想，和排名问题最后一种相同的思想，我们将每个人每天跟他之前的天数做一个笛卡尔积。如下所示：

SELECT

*

FROM

t_users t1

LEFT JOIN t_users t2 ON t1.id = t2.id

AND t1.date >= t2.date

ORDER BY

t1.id;

执行结果如下图所示：

 

最后我们按照t1表字段分组，t2表聚合就可以了。完整的SQL语句如下所示：

SELECT

t1.id,

t1.`name`,

t1.date,

sum( IFNULL( t2.earn, 0 ) - IFNULL( t2.cost, 0 ) ) money

FROM

t_users t1

LEFT JOIN t_users t2 ON t1.id = t2.id

AND t1.date >= t2.date

GROUP BY

t1.id,

t1.`name`,

t1.date

ORDER BY

t1.id;

最终的结果如下图所示：

 

二、SQL提升题目

讲完了上面几种情况，下面咱们用一些题目来巩固咱们的知识。

2.1统计体温

t_sign表为员工每天的体温测量详细表，企业要求员工每日测两次体温，即上午和下午都要进行测温，一个员工一天可以测量多次，但是公司每天只统计上午12点前最早的一次体温，以及下午最后一次体温记录。

 

在该表中，emp_no为员工编号，emp_name为员工姓名，temperature表示测试温度，sign_time表示测温时间。例如第一条数据表示周芷若在2022年8月26号，07:13:27测得的温度为36.5度。

我们需要根据这个表计算出下面的结果：

 

解决思路：

第一步：既然要按照每一天来统计，我们需要添加辅助字段将每天的日期表示出来，如下所示:

select t.*,date(t.sign_time)sign_date from t_sign t;

执行结果如下：

 

第二步：计算每个人上午的最早时间：

同学们，以后只要出现最这个词，想都不要想就一定需要使用聚合函数，比如对这道题，咱们按照sign_date、emp_no以及emp_name三个字段分组，求出每个员工每天上午最早的打卡时间。

SELECT

tt.sign_date,

tt.emp_no,

tt.emp_name,

min( tt.sign_time ) min_signTime

FROM

( SELECT t.*, date( t.sign_time ) sign_date FROM t_sign t ) tt

WHERE

tt.sign_time < CONCAT( date( tt.sign_time ), ' 12:00:00' )

GROUP BY

tt.sign_date,

tt.emp_no,

tt.emp_name;

执行结果如下：

 

第三步：关联t_sign表，求出当前时间当前员工的温度。

SELECT

p1.sign_date,

p1.emp_no,

p1.emp_name,

p2.temperature

FROM

(

SELECT

tt.sign_date,

tt.emp_no,

tt.emp_name,

min( tt.sign_time ) min_signTime

FROM

( SELECT t.*, date( t.sign_time ) sign_date FROM t_sign t ) tt

WHERE

tt.sign_time < CONCAT( date( tt.sign_time ), ' 12:00:00' )

GROUP BY

tt.sign_date,

tt.emp_no,

tt.emp_name

) p1

INNER JOIN t_sign p2 ON p1.emp_no = p2.emp_no

AND p1.min_signTime = p2.sign_time;

第四步：按照前三步的思路，求出每个员工每天下午最后一次的温度：

SELECT

p1.sign_date,

p1.emp_no,

p1.emp_name,

p2.temperature

FROM

(

SELECT

tt.sign_date,

tt.emp_no,

tt.emp_name,

max( tt.sign_time ) max_signTime

FROM

( SELECT t.*, date( t.sign_time ) sign_date FROM t_sign t ) tt

WHERE

tt.sign_time >= CONCAT( date( tt.sign_time ), ' 12:00:00' )

GROUP BY

tt.sign_date,

tt.emp_no,

tt.emp_name

) p1

INNER JOIN t_sign p2 ON p1.emp_no = p2.emp_no

AND p1.max_signTime = p2.sign_time;

第五步：合并两个结果集。

通过子连接的方式将每天每个人上午第一次和最后一次温度合并在同一列，如下所示：

SELECT

am.sign_date '测量日期',

am.emp_no '员工编号',

am.emp_name '员工姓名',

am.temperature '上午第一次温度',

pm.temperature '下午最后一次温度'

FROM

(

SELECT

p1.sign_date,

p1.emp_no,

p1.emp_name,

p2.temperature

FROM

(

SELECT

tt.sign_date,

tt.emp_no,

tt.emp_name,

min( tt.sign_time ) min_signTime

FROM

( SELECT t.*, date( t.sign_time ) sign_date FROM t_sign t ) tt

WHERE

tt.sign_time < CONCAT( date( tt.sign_time ), ' 12:00:00' )

GROUP BY

tt.sign_date,

tt.emp_no,

tt.emp_name

) p1

INNER JOIN t_sign p2 ON p1.emp_no = p2.emp_no

AND p1.min_signTime = p2.sign_time

) am

INNER JOIN (

SELECT

p1.sign_date,

p1.emp_no,

p1.emp_name,

p2.temperature

FROM

(

SELECT

tt.sign_date,

tt.emp_no,

tt.emp_name,

max( tt.sign_time ) max_signTime

FROM

( SELECT t.*, date( t.sign_time ) sign_date FROM t_sign t ) tt

WHERE

tt.sign_time >= CONCAT( date( tt.sign_time ), ' 12:00:00' )

GROUP BY

tt.sign_date,

tt.emp_no,

tt.emp_name

) p1

INNER JOIN t_sign p2 ON p1.emp_no = p2.emp_no

AND p1.max_signTime = p2.sign_time

) pm ON am.emp_no = pm.emp_no

AND am.sign_date = pm.sign_date

ORDER BY

am.emp_no;

执行结果如下所示：

 

2.2统计学生表名次

表为t_student，原始数据如下：

 

在t_student表中，id表示学生学号，name表示学生姓名，gender表示性别，score表示成绩，grade表示其对应的班级序号：

例如：第一条数据表示学号为1001的周芷若同学成绩为98分，班级为1。

我们需要统计排名第1、第5、第7、第10的同学所有信息：如果几个人的分数一样，名次并列，比如夏诗涵、诸葛亮和刘备都是100分，那么他们都是第一名。

我们所需要的结果如下图所示：

 

解决思路：这就是咱们常见的排名问题，咱们使用连接查询计算出的辅助字段rk。

第一步：关联查询计算排名辅助字段rk。

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId,

count( p2.score ) + 1 rk

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.score FROM t_student t ) p2

ON p1.score < p2.score

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

ORDER BY

rk;

第二步：通过rk字段条件筛选出rk等于1、5、7、10的数据：

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.score FROM t_student t ) p2

ON p1.score < p2.score

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

HAVING

count( p2.score ) + 1 IN ( 1, 5, 7, 10 )

ORDER BY

p1.score DESC;

最终的结果如下图所示：

 

2.3统计前五名各班占有比例

继续2-2的的思路，我们可以统计出前5名如下：

 

这一次我们需要加大难度，统计出前5名学生中，每个班的学生个数比例。比如在前5名学生中，一班有3个，二班有1个，三班有4个，那么一班的百分比则为37.5%，二班为12.5%，三班则为50%。

解决思路：

第一步：在2-2的基础上，以gradeId分组，计算出每个班的人数：

SELECT

pp.gradeId,

count( 1 ) num

FROM

(

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.score FROM t_student t ) p2

ON p1.score < p2.score

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

HAVING

count( p2.score ) + 1 BETWEEN 1

AND 5

ORDER BY

p1.score DESC

) pp

GROUP BY

pp.gradeId;

第二步：求出总人数。

SELECT

count( 1 ) total

FROM

(

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.score FROM t_student t ) p2

ON p1.score < p2.score

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

HAVING

count( p2.score ) + 1 BETWEEN 1

AND 5

ORDER BY

p1.score DESC

) pp;

第三步：两个结果集内连接，数量相除：

SELECT

p1.gradeId,

CONCAT( ROUND( num / p2.total, 3 ) * 100, '%' )

FROM

(

SELECT

pp.gradeId,

count( 1 ) num

FROM

(

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.score FROM t_student t ) p2 ON p1.score < p2.score

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

HAVING

count( p2.score ) + 1 BETWEEN 1

AND 5

ORDER BY

p1.score DESC

) pp

GROUP BY

pp.gradeId

) p1

INNER JOIN (

SELECT

count( 1 ) total

FROM

(

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.score FROM t_student t ) p2

ON p1.score < p2.score

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

HAVING

count( p2.score ) + 1 BETWEEN 1

AND 5

ORDER BY

p1.score DESC

) pp

) p2;

执行结果如下：

 

2.4计算每个班的名次

还是对t_student，这次我们需要查询每个班的前三名同学信息，原表信息如下：

 

例如：1班前三名分别为夏诗涵100分、张无忌99分、周芷若98分

2班前三名分别为洪七公98分、杨过91分、郭靖74分

3班前三名分别为诸葛亮100分、赵云100分、刘备96分、貂蝉94分，像诸葛亮和刘备并列都算第一名

我们需要得到以下的结果：

 

解决思路：这也是排名问题，还是相同的逻辑，因为此时分组了，我们需要关联的时候加上gradeId即可：

SELECT

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

FROM

t_student p1

LEFT JOIN ( SELECT DISTINCT t.gradeId, t.score FROM t_student t )

p2 ON p1.score < p2.score

AND p1.gradeId = p2.gradeId

GROUP BY

p1.id,

p1.`name`,

p1.gender,

p1.score,

p1.gradeId

HAVING

count( p2.score ) + 1 BETWEEN 1

AND 3

ORDER BY

p1.gradeId,

p1.score DESC;

2.5统计问题案例

对应的表为t_user，说明如下：

 

t_user表某个人每天的收入支出情况:

userId表示用户编号，username为用户姓名，earn表示收入，cost表示支出，date表示该行为发生时间；

例如：第一条数据表示周芷若在2022/10/01 14:33:48这个时刻赚了1000；

第二条数据表示周芷若在2022/10/01 16:34:41这个时刻赚了300；

第三条数据表示周芷若在2022/10/01 17:35:02这个时刻花了500；

我们想要统计每个用户每天有多少钱：

例如：
周芷若10.1号有三条数据：分别为赚1000、300以及花500，所以周芷若10.1身上的钱为100+300-500=800

同理：周芷若10.2有两条数据，分别为赚200和花500，那么周芷若10.2身上的钱为10.1的钱加上今天的钱也 就是200-500+800=500块。

以此类推：我们需要得到如下结果：

 

解决思路：这其实就是刚开始的统计问题的一种形式，和前面的也相差不大，我们直接使用连接查询完成即可：

SELECT

a.total_date,

a.userId,

a.username,

sum( b.money ) total_money

FROM

(

SELECT

tt.total_date,

tt.userId,

tt.username,

sum( tt.earn - tt.cost ) money

FROM

( SELECT t.*, date( t.date ) total_date FROM t_user t ) tt

GROUP BY

tt.total_date,

tt.userId,

tt.username

) a

INNER JOIN (

SELECT

tt.total_date,

tt.userId,

tt.username,

sum( tt.earn - tt.cost ) money

FROM

( SELECT t.*, date( t.date ) total_date FROM t_user t ) tt

GROUP BY

tt.total_date,

tt.userId,

tt.username

) b ON a.total_date >= b.total_date

AND a.userId = b.userId

GROUP BY

a.total_date,

a.userId,

a.username

ORDER BY

a.total_date,

a.userId;

执行结果如下：

 

2.6阿里面试题：持续登录问题

下表为login_date，表示用户登录信息表。如下图所示：

 

login_date这个表表示用户登录信息，id为用户登录id，days表示登录日期，我们需要统计每个id连续登录的天数，间隔1天也算。

例如：1001连续登录的天数为2022-05-01、2022-05-02、2022-05-04、2022-05-04、2022-05-05。我们就说1001第一次连续登录的天数为5；当前用户1001从2022-05-08开始登录，连续登录的天数为2022-05-08、2022-05-10，此次登录的连续登录天数为3天。

解决思路：本题较为复杂，参考SQL代码如下：

SELECT

*

FROM

(

SELECT

ff.id,

sum( ff.diffs ) + 1 days

FROM

(

SELECT

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id tid,

p2.days tday,

min( p1.diff - IFNULL( p2.diff, 0 ) ) diffs

FROM

(

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p1

LEFT JOIN (

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p2 ON p1.id = p2.id

AND p1.days = p2.days

AND p1.pid = p2.pid

AND p1.diff > p2.diff

WHERE

p1.days = ( SELECT min( t.days ) FROM login_date t )

GROUP BY

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id,

p2.days

) ff

INNER JOIN (

SELECT

ss.id,

ss.days,

min( ss.pday ) pday

FROM

(

SELECT

p1.id,

p1.days,

p1.pday

FROM

(

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p1

LEFT JOIN (

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p2 ON p1.id = p2.id

AND p1.days = p2.days

AND p1.pid = p2.pid

AND p1.diff > p2.diff

WHERE

p1.days = ( SELECT min( t.days ) FROM login_date t )

GROUP BY

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id,

p2.days

HAVING

min( p1.diff - IFNULL( p2.diff, 0 ) ) > 2

) ss

GROUP BY

ss.id,

ss.days

) mm ON ff.id = mm.id

AND ff.days = mm.days

AND ff.pday < mm.pday

GROUP BY

ff.id,

ff.days UNION ALL

SELECT

ll.id,

sum( ll.diffs ) + 1 days

FROM

(

SELECT

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id tid,

p2.days tday,

min( p1.diff - IFNULL( p2.diff, 0 ) ) diffs

FROM

(

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p1

LEFT JOIN (

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p2 ON p1.id = p2.id

AND p1.days = p2.days

AND p1.pid = p2.pid

AND p1.diff > p2.diff

GROUP BY

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id,

p2.days

) ll

WHERE

ll.days IN (

SELECT DISTINCT

pp.pday

FROM

(

SELECT

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id tid,

p2.days tday,

min( p1.diff - IFNULL( p2.diff, 0 ) ) diffs

FROM

(

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p1

LEFT JOIN (

SELECT

t1.id,

t1.days,

t2.id pid,

t2.days pday,

t2.days - t1.days diff

FROM

login_date t1

LEFT JOIN login_date t2 ON t1.id = t2.id

AND t1.days <= t2.days

ORDER BY

t1.days,

t1.id,

t2.days

) p2 ON p1.id = p2.id

AND p1.days = p2.days

AND p1.pid = p2.pid

AND p1.diff > p2.diff

GROUP BY

p1.id,

p1.days,

p1.pid,

p1.pday,

p2.id,

p2.days

) pp

WHERE

pp.diffs > 2

)

GROUP BY

ll.id,

ll.days

) p

ORDER BY

p.id;

执行结果如下：

 

总结

以上是本篇文章讲解的所有内容，本篇文章主要讲解了一些常用的SQL技巧。在咱们开发项目的过程中，数据库开发不可缺少。

本次主要介绍了一些必备的SQL技巧，在咱们开发中，肯定是必不可少的存在，写本篇文章的主要目的呢，主要是给大家拓展一些SQL的技巧和思路，众所周知，数据库最难得最常用的就是读操作，尤其是多表联查，相信让很多同学头疼不已。咱们Java工程师开发一个程序，肯定是围绕数据库开发的，一个库设计得好不好，能够大幅度的改变程序员的开发难度和工作压力。咱们SQL能力提升上去了，Java解析代码就会写得少，比如你对一个查询，需要用多步查询，在程序中就需要反复解析拼接对应的数据，如果SQL写得好，直接在程序中直接返回即可。

很多同学在初学SQL的时候会觉得很难，其实当你用心的去学习，会发现写SQL是一件很有挑战性很有趣的事情，不管是子查询，多表联查，规律都是一样的，举个例子，写SQL就像咱们常玩的2048游戏一样，2变成4、4变成8。就比如咱们有时一个查询很复杂，咱们分步来完成，先得到一个结果集再根据结果集进行连接，直至完成最后的功能为止，当你真正掌握得时候，会有一种很美好的成就感。

咱们学习过程是苦乐交织的，难得东西固然学得比较辛苦，但是呢，往往也是最考验咱们的时候，所以啊，加油吧，未来一定会是美好的！