【LeetCode数据库】题目总结——小技巧

前言

这段时间，刷掉了LeetCode的数据库简单题目，挺有感触的。在刷这些题之前，我只写过一般的增删改查，尤其是查询操作，使用的也不是很灵活，只知道最基本的用法。

去刷了题目之后真的学到了很多不论是新的知识点还是一些组合用法，总之对数据库操作尤其是dql的使用更加熟练了。学会这些小操作后，写sql头不疼了，腰不酸了，睡得着了，吃饭也更香了。

根据这54道题，我总结了一些常用的知识点、小技巧以及部分有代表性的题解。

这篇文章就来分享一些小技巧。也许不算技巧，或者只算是知识点的组合

---

多关键字排序

多关键字排序是很多时候都被要求的操作。在一开始了解到排序语句时就只了解到了通过某个字段排序，是单关键字排序。

面对多关键字排序的需求，一开始确实容易反应不过来，以为是什么新操作。

其实就是老树新花，在原来的排序语句中，使用逗号将多个排序字段隔开即可，每个字段后面还可以跟上各自的顺序。

学生们参加各科测试的次数

题目链接
这道题需要我们每个学生参与每场考试的次数

对于输出结果，有对顺序的需求，需要以学生id为第一关键字，科目名称作为第二关键字，升序排序

由于排序的默认顺序是升序，因此我们在制定指定排序关键字时可以省略排序顺序。

SELECT
    s.student_id,
    s.student_name,
    sb.subject_name,
    COUNT(e.student_id) AS attended_exams
FROM 
    Students s
    join Subjects sb
    LEFT JOIN Examinations e 
    on e.subject_name = sb.subject_name and e.student_id = s.student_id
GROUP BY
    s.student_id,sb.subject_name
ORDER BY
    s.student_id,sb.subject_name;

在group_concat中使用排序

group_concat是分组后常用的一个函数，他能将一个分组内的内容按照一定格式陈列出来。

在陈列内容的时候，往往有排序的需求，像是根据某某字段进行排序等等。

在group_concat中进行排序，只需要直接在所有陈列字段的后面写上排序规则即可，即order by 字段1 (desc),字段2 (desc)...

就像下面这道题：

按日期分组销售产品

题目链接
这个题目要求我们按照销售日期分组，分组后展示所有的销售商品，按照商品名称排序，就正好需要我们根据某个字段进行排序。

select
    sell_date,
    count(distinct product) as num_sold,
    group_concat(distinct product order by product) as products
from 
    activities
group by
    sell_date
;

sum配合case when

case-when-then-else-end这套分支语句是功能非常强大的，更为厉害的是，他可以配合着聚合函数一起使用，对分组中的每一个成员进行条件判断，产生不同的返回。

这种组合也是解决很多题的常用套路。像统计每组中符合xx条件的记录的个数，这些条件往往不便于使用where条件进行筛选。

这些情况有时是希望保留所有的记录并在此基础上进行统计，例如计算占比，向下面两道题：

即时食物配送I

题目链接
这道题需要我们统计所有“即时订单”的占比。当然我们可以写一个子查询，统计所有即时订单的数量。

但是使用聚合函数配合case-when对所有的记录直接进行筛选统计便可以避免使用子查询，进而提高查询效率。

select
    round(sum(case when order_date = customer_pref_delivery_date then 1 else 0 end)/count(delivery_id)*100,2) as immediate_percentage
from 
    delivery
;

广告效果

题目链接
这道题需要我们统计在所有查看过得广告中，被点击的广告的占比。被点击的广告也算是被查看的广告的一部分。

这道题的分子和分母都需要我们使用case-when来进行处理，当然也可以使用where条件将所有非观看广告的操作给过滤掉，这样分母就可以直接统计全部数据了。

select
    ad_id,
    ifnull(round(sum(case when action = 'Clicked' then 1 else 0 end) / sum(case when action = 'Clicked' or action = 'viewed' then 1 else 0 end) * 100,2),0) as ctr
from 
    ads
group by
    ad_id
order by
    ctr desc,ad_id
;

使用union

union可以联合两个查询结果，但是需要其字段名称和类型必须相同。

union算是个挺偏的知识点的。在所有的54题中仅出现过一次以其为较优答案的题目，就是下面这个题。

制作绘画柱状图

题目链接
这道题需要我们制作一个柱状图，其实就是统计各分段的数目。

如果不要求显示所有的分数段而是仅显示存在结果的分段的话，这道题就可以通过公式来控制每个位置。

但是他需要我们统计所有的分数段，这时候我们就可以使用union来解决这个问题。分别查询每个分数段的数据，并使用union联合这些结果即可。

select '[0-5>' as bin,count(*) as total
from sessions
where duration / 60 >= 0 and duration / 60 < 5

union

select '[5-10>' as bin, count(*) as total
from sessions
where duration / 60 >= 5 and duration / 60 < 10

union

select '[10-15>' as bin,count(*) as total
from sessions
where duration / 60 >= 10 and duration / 60 < 15

union

select '15 or more' as bin,count(*) as total
from sessions
where duration / 60 >= 15
;

善用子查询

子查询是解决多数问题的关键步骤。

子查询的结果可能是一张表，它帮助我们先一步进行一次筛选、分组、计算等等。

子查询的结果也可能是一列或几列数据，他可以当做我们in运算的结果集合。

子查询的结果还可能是一个值，他可能帮助我们为某张表做了某种计算，提供了一个可参考的数值。

下面就罗列一些在刷题中遇到的子查询的用法：

配合in语句

这其实一个老生常谈的问题了。作为条件的集合，是子查询的作用之一，也是相当重要的一个作用。

一般的用法就是使用子查询查询出一列数据，将这一列数据作为集合配合in语句对元素进行筛选。

销售分析II

题目链接
这道题需要我们统计所有购买过s8却没有购买过iphone的用户。

我们可以使用两个子查询，一个查询出所有购买了s8的用户，另一个查询出所有购买了iphone的用户，这个去个补集就是所有没有购买过iphoe的用户。

最后取两个集合的交集就行

select 
    distinct buyer_id
FROM
    Sales
where
    buyer_id 
    in(
    SELECT 
        distinct buyer_id
    FROM
        Sales s
        JOIN Product p
        ON p.product_id = s.Product_id
    where
        product_name = 'S8'
    )
    and
    buyer_id not in(
    SELECT 
        distinct buyer_id
    FROM
        Sales s
        JOIN Product p
        ON p.product_id = s.Product_id
    where
        product_name = 'iphone')
;

除了匹配单个字段，in语句还可以从子查询中同时匹配多个字段，相当于将两个字段集合的笛卡尔积中的元素拿出来作为元素。这时就需要子查询中要有数量相同，字段名称相同，类型相同的字段。

多关键字匹配的写法也非常简单，使用逗号隔开就行，记得括起来。

游戏玩法分析II

题目链接
这道题需要我们统计玩家的首次登陆设备。

我们需要先查询出所有玩家第一次登录的时间，这个查询最终呈现的关键字有两个，即玩家id和日期。

在使用in语句筛选时，我们就需要玩家id和日期两个关键字同时匹配

select
    player_id,
    device_id
from 
    activity
where
    (player_id,event_date) in(
        select  
            player_id,
            min(event_date)
        from
            activity
        group by
            player_id
    );

作为元素进行运算

这个就是子查询结果是一个单独的数值的结果了。我们可以从下面这道题来看到它的作用。

好友申请I：总体通过率

题目链接
这道题需要我们分别从两个表中查询出不重复的数据数量，最后的出比值。

我们可以使用子查询，使其返回结果为数据数量。

这个子查询的结果就是单行单列的一个数据，可以直接被我们作为数值参与运算

select
    round(ifnull(
        (select count(distinct requester_id,accepter_id) from request_accepted)/
        (select count(distinct sender_id,send_to_id) from friend_request)
        ,0),2) as accept_rate
;

使用自联结

自联结是很常见的一种处理方式啦，往往出现在那些需要在同一张表中比较同一字段的不同行并进行统计的需求中。

我们的做法就是将目标表单进行自联结，并使用where条件将符合统计标准的组合留下来，将不需要进行比较的组合筛选掉。

之后，同一字段的不同行比较变成了同一行不同字段的比较，难度直线下降。

我们来看这一题：

连续空余座位

题目链接
这道题需要我们比较每个空座位的相邻元素是否是空座位，是典型的同一字段不同行的比较。

我们使用自联结分别连接前面座位和后面的座位

这样前后座位的信息和当前座位的信息就都汇聚到一行中了。直接进行比较就行

筛选出所有自己是空位并且上面或者下面是空位座位id

select
    c1.seat_id
from 
    cinema c1
    left join cinema c2 on c1.seat_id = c2.seat_id - 1
    left join cinema c3 on c1.seat_id = c3.seat_id + 1
where
    c1.free = 1
    and
    (
        c2.free = 1
        or 
        c3.free = 1
    )
;

---

数据库题解

· 【LeetCode数据库】 题目总结——经典题目题解与分析（一）
· 【LeetCode数据库】 题目总结——经典题目题解与分析（二）
· 【LeetCode数据库】 题目总结——经典题目题解与分析（三）