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

ffscu2ro  于 2023-05-21  发布在  Mysql
关注(0)|答案(6)|浏览(219)

我正在练习SQL语言,遇到了这样一个问题:
编写一个SQL查询,查找至少连续出现三次的所有数字。

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

例如,给定上面的Logs表,1是唯一连续出现至少三次的数字。
我在网上找到了一个解决方案,并进行了测试。但我真的不明白。解决方案的大局是清楚的。sq表统计出现次数。但是我不明白计算sq的部分。我对MySQL做了很多研究。@counter := IF(@prev = Num, @counter + 1, 1)表示如果prev = Num,则计数器=计数器+1,否则计数器= 1。(SELECT @counter:=1, @prev:=NULL) vars表示创建一个表vars,其中包括两列counterpre
谁能帮我解释一下sq部分的逻辑?或者在SELECT中有关于这类表达式的教程吗?我对SQL完全陌生,我知道这个问题可能很简单。谢谢你的帮助!

SELECT  DISTINCT(Num) AS ConsecutiveNums
FROM (
    SELECT
    Num,
    @counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
    @prev := Num
    FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars
) sq
WHERE how_many_cnt_in_a_row >= 3
z4bn682m

z4bn682m1#

让我们浏览一下每条记录,看看这个查询是如何工作的。写得很好。

SELECT...FROM

SELECT...FROM Logs y, (...) vars是什么意思
如果你有一张这样的table:create table test(field1 int)包含3行,如下所示:

field1
-------
1
2
3

执行select * from test, (select @counter:=1, @prev:=NULL) vars将导致

field1  @counter:=1  @prev=NULL
------- ------------ -----------
1       1            NULL
2       1            NULL
3       1            NULL

@counter@prev是会话变量。它们分别初始化为1和NULL。所有行都与这些变量组合在一起,以给予上面所看到的内容。

子查询的逐行分析

只关注这个子查询。

SELECT
Num,
@counter := IF(@prev = Num, @counter + 1, 1) AS how_many_cnt_in_a_row,
@prev := Num
FROM Logs y, (SELECT @counter:=1, @prev:=NULL) vars

查询选择ID=1,Num=1的第一行,并选择Num作为其第一列。
对于第二列,它做了一些数学运算。它检查是否@prev = Num。好吧,@prev是NULL,因为这就是它的初始化方式。因此,@prev = Num的结果为false。IF通常写为IF(condition, what-to-do-if-condition-is-true, what-to-do-if-condition-is-false)

IF(@prev = Num, @counter + 1, 1)
   -----------  ------------  --
   condition    do this       do this if condition
                if true       is false

由于@prev为NULL且不等于Num,因此返回1。
对于第3列,查询仅将@prev重置为Num。就这些现在让我们来看看SELECT是如何逐行执行并发挥其魔力的。

Num  @prev was  @counter was  @counter calculation      @prev reset to Num
---  ---------  ------------  -----------------------   ------------------
1    NULL       1             is @prev = 1? No. So 1      1
1    1          1             is @prev = 1? Yes! So 2     1
1    1          2             is @prev = 1? Yes! So 3     1
2    1          3             is @prev = 2? No. So 1      2
1    2          1             is @prev = 1? No. So 1      1
2    1          1             is @prev = 2? No. So 1      2
2    2          1             is @prev = 2? Yes! So 2     2

上面的第2列和第3列仅用于理解目的。
现在子查询已经完成了它的工作,SELECT DISTINCT...来询问:从上面的结果中,只给予@counter为3或更高的行。结果将会是

Num   @counter  @prev
----  --------  -----
1     3         1

如果你的数据集有五个1,那么第三个、第四个和第五个1将被检索。因此,DISTINCT(Num)用于仅选择单个1。这只是聪明的想法。可以将WHERE子句改为WHERE ... = 3,而不是>= 3
希望这有意义。

pgky5nke

pgky5nke2#

首先,下面这行代码只是初始化变量@counter@prev。有关它的更多信息,请查看User Defined Variables

(SELECT @counter:=1, @prev:=NULL)

因此,sq不是一个实际的表,但它可以作为别名工作,以便您可以引用这些内存中的变量。@counter变量计数有多少个数字是连续的顺序,当前一个数字@prev与实际的Num不同时,@counter被重置为1,计数过程再次开始。
为了更清楚,下面是sq的值:
+-----+-----------------------+ | Num | how_many_cnt_in_a_row | +-----+-----------------------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 2 | 1 | | 1 | 1 | | 2 | 1 | | 2 | 2 | +-----+-----+

x0fgdtte

x0fgdtte3#

请尝试以下查询:

select ConsecutiveNums from(    
        select 
        case 
        when lag(Num) over (order by Id) = Num and Num=lead(Num) over (order by 
        Id)  then Num 
        end as ConsecutiveNums
        from Logs
        )  
where ConsecutiveNums is not null
t40tm48m

t40tm48m4#

下面是另一个版本的答案……

select distinct num 
  from ( 
          select num
                ,lag(num,1) over() as lag1
                ,lag(num,2) over() as lag2
             from
                 logs
       ) as a
   where  num = lag1 = lag2
     and  lag1 = lag2
igsr9ssn

igsr9ssn5#

SELECT 
    distinct A.num as ConsecutiveNums 
FROM
    LOGS A
INNER JOIN Logs B
    on A.id=b.id+1
INNER join Logs C
    on b.id=c.id+1
WHERE a.num=b.num AND a.num=c.num
;
0kjbasz6

0kjbasz66#

SELECT DISTINCT Num FROM(SELECT Num,LAG(Num)OVER(ORDER BY Id)AS prev_num,LEAD(Num)OVER(ORDER BY Id)AS next_num FROM your_table)AS子查询WHERE Num = prev_num AND Num = next_num;

相关问题