【LeetCode数据库】题目总结——经典题目题解与分析（九）--中等难度

最新推荐文章于 2024-05-23 20:42:21 发布

卷儿~

最新推荐文章于 2024-05-23 20:42:21 发布

阅读量384

点赞数

分类专栏： # LeetCode数据库文章标签：数据库 sql

本文链接：https://blog.csdn.net/wayne_lee_lwc/article/details/108509244

版权

LeetCode数据库专栏收录该内容

10 篇文章 4 订阅

订阅专栏

刷完了简单和中等难度的数据库题目，对sql中的一些语法特性有了简单的了解，算是学会了挺多思路，还有很多的小技巧

总之就是感觉使用sql能完成的任务越来越多了，能解决的业务场景也也越来越复杂了。有许多之前不得不拿到程序中才能处理的数据使用简单的sql就能够达成，当事人就很有成就感。

所以刷题整理这个过程还会持续下去，从这篇博文开始，就是中等难度的了，由于题目较长或者题解较简单稍微复杂。所以为了避免每篇博文过于冗长，暂定是5-6道题一篇。会挑选博主觉得比较有意义的题目（根据博主不算高的水平）

往期的题解会放在最后，刷题总结会持续更新，来关注博主一起进步啊~

餐馆营业额变化增长

题目链接
在这里插入图片描述
这个题需要我们统计每个连续七天的顾客消费平均值。

首先需要用自联结来绑定以每个日期为止的七天，然后根据这些日期分组。

有些日期前面不足七天，可以先找出最早的日期，然后筛选出离这个日期至少多出6天的日期。

剩下的日期日期就都是具有前七天的值了。拿这张表同customer表进行联结七天内的日期记录。

最后按照日期分组，求和，取平均（除以7）

# Write your MySQL query statement below
select  
    dates.visited_on,
    sum(c.amount) as amount,
    round(sum(c.amount) / 7,2) as average_amount
from
(
    select 
        distinct visited_on
    from   
        customer
    where 
        datediff(visited_on,
    (
        select
            visited_on
        from 
            customer
        order by    
            visited_on
        limit 0,1
    )) >= 6
) as dates left join customer c on datediff(dates.visited_on,c.visited_on) <= 6 and datediff(dates.visited_on,c.visited_on) >= 0

group by 
    dates.visited_on
order by    
    dates.visited_on

电影评分

题目链接
在这里插入图片描述
这道题需要我们查询出两个结果，一个是评论数量最多的用户名称，第二个是二月平均分最高的电影名称。

两个查询关系不大，可以各自查询出来，再使用union进行联结。

查询评论最多的用户名称，联结用户表，按照用户分组，然后按照评论数排序，取最大

查询平均分最高，联结电影表，限定二月的评论，按照电影分组，然后按照平均分排序，取最大

# Write your MySQL query statement below
(
select
    name as results
from 
    movie_rating,
    users
where
    users.user_id = movie_rating.user_id
group by 
    movie_rating.user_id
order by    
    count(movie_id) desc,name
limit 0,1
)
union 
(
select
    title as results
from 
    movie_rating,
    movies
where
    movies.movie_id = movie_rating.movie_id
    and
    created_at like '2020-02%'
group by
    movie_rating.movie_id
order by    
    sum(rating) / count(user_id) desc,title
limit 0,1
)

顾客的可信联系人数量

题目链接
在这里插入图片描述
这道题需要我们查询出每张支票的价值，用户信息，用户的联系人数量，用户的信任联系人数量

支票的信息直接就在支票表中储存着，用户信息可以单独处理后再与支票表进行联结。

用户的联系人信息可以直接在分组后统计得出，需要注意的就是空值情况。

用户的信任联系人数量实在联系人的基础上再进行一次筛选，需要是商店的顾客，这样的话可以在聚合函数中使用case语句判断一个顾客是否是商店的顾客，这个判断可以使用子查询来获得联系人集合。

# Write your MySQL query statement below

select  
    invoice_id,
    customer_name,
    price,
    contacts_cnt,
    trusted_contacts_cnt
from 
    invoices i
    left join(
        select
            cus.customer_id as id,  
            customer_name,
            ifnull(count(distinct contact_name),0) as contacts_cnt,
            sum(
                case
                    when contact_name in (select customer_name from customers) then 1
                    else 0
                end
            )as trusted_contacts_cnt
        from 
            customers cus
            left join contacts con on con.user_id = cus.customer_id
        group by
            cus.customer_id
    ) a on i.user_id = a.id
order by 
    i.invoice_id

计算布尔表达式的值

题目链接
在这里插入图片描述
这道题很有意思，让我们计算一张表中的布尔表达式的值，其中两个变量字段的值在另一张表上。

首先先联结变量值的表，联结两次，分别是左值和右值。后面的事情就要交给case语句了，有了左值和右值，我们直接根据中间运算符进行运算即可。

# Write your MySQL query statement below

select
    left_operand,
    operator,
    right_operand,
    (
        case
            when operator = '=' then if(vl.value = vr.value,'true','false') 
            when operator = '<' then if(vl.value < vr.value,'true','false') 
            when operator = '>' then if(vl.value > vr.value,'true','false') 
        end
    ) as value
from    
    expressions e 
    left join variables vl on e.left_operand = vl.name
    left join variables vr on e.right_operand  = vr.name
;

活跃用户

题目链接
在这里插入图片描述
这个题让我们查找出“活跃用户”，所谓活跃用户是指存在连续五天登录行为的用户。

如果直接自联结并按照用户分组，查找出每天之前的登录记录并进行筛选的话就会不符合连续五天的路的要求。

因此可以自联结后按照用户和日期进行分组，同时筛选出过去五天具有五次登录记录的日期（需要注意的是，可能存在一天登录多次的情况，这时候需要在统计的时候使用去重，只统计不同的日期数）

筛选完之后，我们得到的，是所有满足连续五天登录的用户id及其达成的日期，得到所有用户id并进行去重即可。

在得到用户id后由于还需要得到用户的姓名，那就再与用户表进行一次联结，联结后再进行输出即可。

# Write your MySQL query statement below
select
    id.id,
    a.name
from    
    (
        select  
            distinct l1.id
        from 
            logins l1
            join logins l2 on l1.id = l2.id and datediff(l1.login_date,l2.login_date) < 5 and datediff(l1.login_date,l2.login_date) >= 0
        group by
            l1.id,l1.login_date
        having 
            count(distinct l2.login_date) >= 5
    ) id left join accounts a on a.id = id.id