乐趣区

关于mysql:day4-体育馆的人流

jiezi

本题应用的是 MySQL8.0，没有在 MySQL5.6 版本中测验过，不保障正确。

题目

题目起源：体育馆的人流

查找出每行人数大于 100 且 id 间断的三行或者更多好记录 

create table stadium (
    id int,
    visit_date date,
    people int
)

insert into stadium values
(1, '2017-01-01', 10),
(2, '2017-01-02', 109),
(3, '2017-01-03', 150),
(4, '2017-01-04', 99),
(5, '2017-01-05', 145),
(6, '2017-01-06', 1455),
(7, '2017-01-07', 199),
(8, '2017-01-09', 188);

SQL：办法一 

select * from (select *, count(*) over(partition by diff) as counts from (select *, id - row_number() over(order by id) as diff from stadium
        where people >= 100
    ) as base
) as s where counts >= 3;

解析

问题的关键在于如何晓得哪几天的 id 是间断的。

这里用的办法是先筛选出 peope 大于等于 100 的数据，而后对这些数据进行排名，在用 id 减去排名，如果他们之间的差一样，就阐明那几天是间断的。

具体步骤：

  • 先筛选出 people 大于等于 100 的数据
  • 应用 row_number() 对 id 计算出排名
  • 在用 id 减去排名，计算出 id 和排名之间的差（作为长期表 base）

  • 对 base 进行查问并依照 diff 进行分组，命名为 counts（作为长期表 s）

    • 这里应用 over(partition by diff) 比 group by 更加精确。因为 group by 须要和 select 字段一一对应。
  • 对 s 表进行查问，筛选出 counts 大于等于 3 的数据

SQL：办法二 

select * from (select *, (id - (@rrk:=@rrk + 1)) as diff
    from stadium, (select @rrk:=0) as init where people >= 100
) as s1 where diff in (
    select diff from (select (id - (@rk:=@rk + 1)) as diff
        from stadium, (select @rk:=0) as init where people >= 100
    ) as s2 group by diff having count(*) >= 3
);

解析

和办法一的思路是一样的，实现的形式不一样，这里是通过 group by 进行分组，所以绝对于应用 partition by 的形式步骤更加简单一点。

  • 先筛选出 people 大于等于 100 的数据。
  • 而后应用变量计算出 diff（也能够应用 row_number()），作为长期表 s1。
  • 查问 s1 表，筛选出咱们想要的数据
  • 这里咱们想到如果有一个 diff 的分组就好了，咱们能够通过 in 来筛选。
  • 这一步就是反复下面计算 diff 的步骤，而后作为长期表 s2。

  • 这里里面还要在包一层查问 diff，就是 select diff from s2，应用 group by 对 diff 进行分组，并用 having 筛选出大于等于 3 的 diff

    • group by 须要和 select 字段一一对应，如果不做这一次查问，应用 group by 将会有问题。
  • 查问 s1 表，应用 in 操作符，就能把数据查出来了。

Tips

row_number() 语法

在一条 SQL 中不能用两个一样的变量

更多解题参考：https://github.com/astak16/bl…

退出移动版