MYSQL连续问题

最新推荐文章于 2024-05-28 21:51:56 发布

Avasla

最新推荐文章于 2024-05-28 21:51:56 发布

阅读量789

点赞数 2

分类专栏： Mysql 文章标签： sql mysql

本文链接：https://blog.csdn.net/WHYbeHERE/article/details/108663254

版权

Mysql 专栏收录该内容

51 篇文章 10 订阅

订阅专栏

问题一：连续空余座位

几个朋友来到电影院的售票处，准备预约连续空余座位。利用表 cinema ，写一个查询语句，获取所有空余座位，并将它们按照 seat_id 排序后返回。
注意：

seat_id 字段是一个自增的整数，free 字段是布尔类型（‘1’ 表示空余， ‘0’ 表示已被占据）。
连续空余座位的定义是大于等于 2 个连续空余的座位。

| seat_id | free |
|---------|------|
| 1       | 1    |
| 2       | 0    |
| 3       | 1    |
| 4       | 1    |
| 5       | 1    |

解法一：自连接（使用Join 和 abs）

解题思路：
1）将表格自连接，得到两个表的笛卡尔乘积，如下图（注意：这里只放了一部分）
在这里插入图片描述

放个百度百科的图片帮助理解：

2）为了找到连续空座位，a.seat_id 和b.seat_id应该是相邻的两个数用错位相减法on abs(a.seat_id - b.seat_id)=1；且两者都应该为空and a.free = true and b.free = true
3）防止出现重复值要加distinct，比如34和45都满足条件，4会出现2次

select distinct a.seat_id
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true
order by a.seat_id

解法二：自连接+where

解题思路：和解法一思路基本一致，改成用where 筛选。

select distinct c1.seat_id as seat_id
from cinema as c1,cinema as c2
where (c1.seat_id=c2.seat_id-1 or c1.seat_id=c2.seat_id+1)
and c1.free =1
and c2.free=1
order by seat_id

解法三：使用窗口函数

解题思路：使用Lag()和lead()函数

#第一种窗口函数
select seat_id
from (
    select
        seat_id ,
        free ,
        lag(free,1,999) over() pre_free,
        lead(free,1,999) over() next_free
    from  cinema
)tmp
where  free=1 and (pre_free=1 or next_free=1 )
order by  seat_id

#第二种窗口函数
SELECT C.seat_id
FROM
(SELECT seat_id,
sum(free = 1) OVER (ORDER BY seat_id ASC rows 1 preceding) AS r1,
sum(free = 1) OVER (ORDER BY seat_id ASC rows between 1 preceding and 1 following) AS r2,
sum(free = 1) OVER (ORDER BY seat_id ASC rows between 0 preceding and 1 following) AS r3
FROM cinema) AS C
WHERE C.r1 = 2 OR C.r2 = 3 OR C.r3 = 2;

问题二：连续 5 天登录的用户

写一个 SQL 查询, 找到活跃用户的 id 和 name。活跃用户是指那些至少连续 5 天登录账户的用户。返回的结果表按照 id 排序.

后续问题:如果活跃用户是那些至少连续 n 天登录账户的用户, 你能否写出通用的解决方案?

Accounts 表:
+----+----------+
| id | name     |
+----+----------+
| 1  | Winston  |
| 7  | Jonathan |
+----+----------+

Logins 表:
+----+------------+
| id | login_date |
+----+------------+
| 7  | 2020-05-30 |
| 1  | 2020-05-30 |
| 7  | 2020-05-31 |
| 7  | 2020-06-01 |
| 7  | 2020-06-02 |
| 7  | 2020-06-02 |
| 7  | 2020-06-03 |
| 1  | 2020-06-07 |
| 7  | 2020-06-10 |
+----+------------+

解法1: 定义变量

SELECT
    DISTINCT b.id,b.name
FROM
    (SELECT
        id,
        CASE 
        WHEN @id = id 
        	AND @prev_date=subdate(login_date, interval 1 day) 
        	AND (@prev_date:= login_date) IS NOT NULL 
        	THEN @cnt := @cnt+1
        	
        WHEN @id = id 
        	AND @prev_date=subdate(login_date, interval 0 day) 
        	THEN @cnt := @cnt
        	
        WHEN (@id := id)IS NOT NULL 
        	AND(@prev_date:= login_date) IS NOT NULL 
        	THEN @cnt:=1 
        END AS cnt
    FROM 
        (SELECT * FROM logins ORDER BY id,login_date) a,
        (SELECT @prev_date:=NULL, @cnt:= NULL, @id:= NULL) t) a
    LEFT JOIN accounts b ON a.id = b.id
WHERE a.cnt>=5

解法2：窗口函数

解题思路：
1、原表GROUP BY 分组；
2、使用窗口函数ROW_NUMBER()添加序号；
3、计算登录日期与序号的差值，并以差值重新分组，筛选连续5天登录的用户；
4、对id去重；
5、LEFT JOIN 关联姓名即可。

SELECT t4.id, name
FROM
(
    SELECT DISTINCT id
    FROM
    (
        SELECT id, SUBDATE(login_date, rn) AS diff
        FROM
        ( #对日期进行排序
        SELEC id ,login_date ,ROW_NUMBER() OVER(PARTITION BY id ORDER BY login_date) AS rn 
            FROM
            #获取去重的表格数据
            (SELECT id, login_date FROM Logins GROUP BY id, login_date)t1
        ) t2
        GROUP BY id, diff
        HAVING COUNT(*) >= 5
    ) t3
) t4
JOIN Accounts t5 ON t4.id = t5.id
ORDER BY id

解法3：自连接

解题思路：
– DATEDIFF(L2.login_date,L1.login_date) BETWEEN 0 AND 4 ：对每个id相同的日期都只连接相差4天之内的日期。即对每个日期都检索一下是否有5个连续的日期。
– HAVING COUNT(DISTINCT L2.login_date)=5：只要5天之内，日期有一个不连续，那么去重后每一组的天数必然少于5天。

SELECT DISTINCT A.*
FROM Accounts A
JOIN logins L1 USING(id)
JOIN Logins L2 ON L1.id=L2.id AND DATEDIFF(L2.login_date,L1.login_date) BETWEEN 0 AND 4  
GROUP BY A.id,A.name,L1.login_date
HAVING COUNT(DISTINCT L2.login_date)=5

题目三：连续出现的数字

编写一个 SQL 查询，查找所有至少连续出现三次的数字。
例如，给定的 Logs 表， 1 是唯一连续出现至少三次的数字。

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

解法一：直接方法

连续出现的意味着相同数字的 Id 是连着的，由于这题问的是至少连续出现 3 次，我们使用 Logs 并检查是否有 3 个连续的相同数字。

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id - 1
    AND l2.Id = l3.Id - 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num;

解法二：窗口函数

#1）使用LAG
SELECT DISTINCT Num as ConsecutiveNums
FROM(
    SELECT Id,Num,
    LAG(Num,1)OVER(ORDER BY Id) as num_1,
    LAG(Num,2)OVER(ORDER BY Id) as num_2
    FROM Logs
) as c
WHERE c.num = c.num1 and c.num1 = c.num2

# 2）也可以用LEAD替换LAG
select distinct num as ConsecutiveNums  
from(
    select num,
    lead(num,1)over()as num1,
    lead(num,2)over()as num2 
    from logs
) as c
where c.num = c.num1 and c.num1 = c.num2

解法三：变量

select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

题目一链接：https://leetcode-cn.com/problems/consecutive-available-seats
题目二链接：https://leetcode-cn.com/problems/active-users
题目三连接：https://leetcode-cn.com/problems/consecutive-numbers