通知
关于网站更多信息请加whimurmur模板/jpress插件QQ群(1061691290)            网站从因情语写改为晴雨            这个网站的模板也从calmlog_ex改为 whimurmur

leetcode数据库 系列题 连续问题

790人浏览 / 0人评论 / | 作者:因情语写  | 分类: 数据库  | 标签: 数据库  /  leetcode  | 

作者:因情语写

链接:https://www.proprogrammar.com/article/633

声明:请尊重原作者的劳动,如需转载请注明出处


    180. 连续出现的数字

编写一个 SQL 查询,查找所有至少连续出现三次的数字。

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

例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
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;

    同表连接,连接上一行,上上一行

    再看另一种比较快的解法

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

    使用了变量,有个技巧,(@prev:=Num) is not null,这里在赋值的同时进行了判断,即赋值后会返回赋值的结果

    603. 连续空余座位

几个朋友来到电影院的售票处,准备预约连续空余座位。

你能利用表 cinema ,帮他们写一个查询语句,获取所有空余座位,并将它们按照 seat_id 排序后返回吗?

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

对于如上样例,你的查询语句应该返回如下结果。

| seat_id |
|---------|
| 3       |
| 4       |
| 5       |
select distinct c1.seat_id
from(
        select seat_id
        from cinema
        where free = 1
    ) c1 inner join cinema c2 on c2.free = 1 and (c1.seat_id = c2.seat_id + 1 or c1.seat_id = c2.seat_id - 1)
order by c1.seat_id

    两张空余座位表,连续即当前位置的前一个位置或后一个位置也是空余的

    再看另一种比较快的解法

SELECT distinct(c1.seat_id) 
FROM cinema c1 JOIN cinema c2 ON abs(c2.seat_id-c1.seat_id)=1
WHERE c1.free=1 AND c2.free =1
ORDER BY seat_id

    与上面的解法大同小异,只是利用绝对值函数来判断前或后一个是空位置

    1225. 报告系统状态的连续日期

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.

Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.

系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。

编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期(start_date 和 end_date)。即如果任务失败了,就是失败状态的起止日期,如果任务成功了,就是成功状态的起止日期。

最后结果按照起始日期 start_date 排序

查询结果样例如下所示:

Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+

Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+


Result table:
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+

结果忽略了 2018 年的记录,因为我们只关心从 2019-01-01 到 2019-12-31 的记录
从 2019-01-01 到 2019-01-03 所有任务成功,系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败,系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功,系统状态为 "succeeded"。

 

select r.period_state, case when r.start_date < str_to_date('2019-01-01', '%Y-%m-%d') then str_to_date('2019-01-01', '%Y-%m-%d')
    else r.start_date end start_date, case when r.end_date > str_to_date('2019-12-31', '%Y-%m-%d') then str_to_date('2019-12-31', '%Y-%m-%d')
    else r.end_date end end_date
from(
    select 'failed' period_state, fs.fail_date start_date, fe.fail_date end_date
    from(
        select fi.fail_date, @s1:=@s1+1 seq
        from(
            select f.fail_date
            from Failed f
            where not exists(
                select 1
                from Failed
                where fail_date = date_sub(f.fail_date, interval 1 day)
            )
            order by f.fail_date
        ) fi, (select @s1:=0) st
    ) fs inner join (
        select fi.fail_date, @s2:=@s2+1 seq
        from(
            select f.fail_date
            from Failed f
            where not exists(
                select 1
                from Failed
                where fail_date = date_add(f.fail_date, interval 1 day)
            )
            order by f.fail_date
        ) fi, (select @s2:=0) st
    ) fe on fs.seq = fe.seq
    union
    select 'succeeded', ss.success_date, se.success_date
    from(
        select si.success_date, @s3:=@s3+1 seq
        from(
            select s.success_date
            from Succeeded s
            where not exists(
                select 1
                from Succeeded
                where success_date = date_sub(s.success_date, interval 1 day)
            )
            order by s.success_date
        ) si, (select @s3:=0) st
    ) ss inner join (
        select si.success_date, @s4:=@s4+1 seq
        from(
            select s.success_date
            from Succeeded s
            where not exists(
                select 1
                from Succeeded
                where success_date = date_add(s.success_date, interval 1 day)
            )
            order by s.success_date
        ) si, (select @s4:=0) st
    ) se on ss.seq = se.seq
) r
where r.end_date >= str_to_date('2019-01-01', '%Y-%m-%d') and r.start_date <= str_to_date('2019-12-31', '%Y-%m-%d')
order by r.start_date

    上面分别处理失败与成功的情况,通过union连接,对一个日期,如果不存在上一个日期的记录,就为开始日期,如果不存在下一个日期的记录,就为结束日期,同时要限制一下日期在2019年内,学习了变量的使用,not exists的使用,date_add,date_sub的使用,str_to_date的使用,通过变量生成一个额外的列用来联系开始和结束日期,来降低时间消耗

    下面看另一种较快的解法

select t3.period_state as period_state,min(date) as start_date,
max(date) as end_date from
(select date,period_state,
if(@prev = period_state ,@tmp := @tmp, @tmp := @tmp + 1) as rank,
@prev := period_state as prev
from
(select date,period_state from (
	select fail_date as date,'failed' as period_state from failed
	where fail_date between '2019-01-01' and '2019-12-31'
	union all
	select success_date as date,'succeeded' as period_state from Succeeded
	where success_date between '2019-01-01' and '2019-12-31'
	) t1 order by t1.date asc) t2,(select @prev := null,@tmp := 0) as init) t3
    group by rank

     对这种比较复杂的题目,一般还是要用到变量,这里用了迭代的变量@prev,还用一个变量@tmp做加1运算,这是一种常见的用法,这里是对2019的情况排序,多加一列rank,对连续的成功或失败,rank值是一样的, 还用到了between...and,asc来排序(可省略),别名用as引出(可省略)

    1285. 找到连续区间的开始和结束数字

表:Logs

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| log_id        | int     |
+---------------+---------+
id 是上表的主键。
上表的每一行包含日志表中的一个 ID。

后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。

将查询表按照 start_id 排序。

查询结果格式如下面的例子:

Logs 表:
+------------+
| log_id     |
+------------+
| 1          |
| 2          |
| 3          |
| 7          |
| 8          |
| 10         |
+------------+

结果表:
+------------+--------------+
| start_id   | end_id       |
+------------+--------------+
| 1          | 3            |
| 7          | 8            |
| 10         | 10           |
+------------+--------------+
结果表应包含 Logs 表中的所有区间。
从 1 到 3 在表中。
从 4 到 6 不在表中。
从 7 到 8 在表中。
9 不在表中。
10 在表中。
select a.log_id as START_ID ,min(b.log_id) as END_ID from 
(select log_id from logs where log_id-1 not in (select * from logs)) a,
(select log_id from logs where log_id+1 not in (select * from logs)) b
where b.log_id>=a.log_id
group by a.log_id

    学习not in的用法,开始日期的上一天记录不存在,结束日期的下一天记录不存在,,结束日期不小于开始日期(应为不小于开始日期的所有结束日期中最小的)

    下面再看另一种较快的解法

select
   s.log_id as START_ID,
   min(e.log_id) as END_ID 
from 
(select distinct a.log_id from Logs a left join Logs b on a.log_id + 1 = b.log_id where b.log_id is null) e,
(select distinct a.log_id from Logs a left join Logs b on a.log_id - 1 = b.log_id where b.log_id is null) s
where s.log_id <= e.log_id 
group by 1

    大同小异,思想是一样的,学习了left join和group by的用法,group by 1表示按select中的第1列分组,类似的还有group by num1, num2,order by 也有此种用法,order by num1, num2这样,表示按select中的第num1, num2列排序


亲爱的读者:有时间可以点赞评论一下

点赞(0) 打赏

全部评论

还没有评论!
广告位-帮帮忙点下广告