题目描述
X 市建了一个新的体育馆,每日人流量信息被记录在这三列信息中:序号 (id)、日期 (date)、 人流量 (people)。
请编写一个查询语句,找出高峰期时段,要求连续三天及以上,并且每天人流量均不少于100。
例如,表 stadium
:
+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 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-08 | 188 |
+------+------------+-----------+
对于上面的示例数据,输出为:
+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
Note:
每天只有一行记录,日期随着 id 的增加而增加。
代码
select
stadium.*
from stadium
inner join
(
select
id-rownum as diff
,group_concat(id) as idlist
,length(group_concat(id))-length(replace(group_concat(id),',','')) #计算逗号的个数,逗号个数为2时表示三个id
from
(
select
@rownum:=@rownum+1 as rownum
,id
from stadium,(select @rownum :=0) t
where people>=100
)t
group by id-rownum
having length(group_concat(id))-length(replace(group_concat(id),',',''))>=2 #表示至少出现三个id
)t
on find_in_set(stadium.id,t.idlist)
题目描述
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs
表, 1
是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
代码
/* Write your T-SQL query statement below */
select
distinct num as consecutivenums
from
(
select
a.num as num
,case when a.num=b.num and b.num = c.num then 1
else 0
end as is_consecutive
from logs a
left join logs b
on a.id+1 = b.id
left join logs c
on a.id+2 = c.id
where c.id is not null
)t
where is_consecutive=1
##问题:如果需要找连续十次出现的数字,那需要十张表相连,不太科学,可以尝试使用用户变量
# Write your MySQL query statement below
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
) temp
where temp.cnt >= 3