[SQL]SQL中使用变量简化操作的案例

题目描述

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