I'm doing the leetcode SQL problems and kind of confused with the code below:
select distinct Num from (
select
Num,
case
when @prevNum = Num then @count := @count + 1
when (@prevNum := Num) is not null then @count := 1
end n
from Logs, (select @prevNum := NULL) r
order by Id
) a where n >= 3
Two questions:
First when (@prevNum := Num) is not null what is the logic value of assignment expression in SQL? I have googled MySQL and Oracle reference book, this forum and I got nothing about it. And this condition is really confusing.
Second the n after case end statement, I know n stands for the alias of case statement, but there are two variables in case end statement here, how do SQL know that n stands for @count but not @prevNum?
Thanks
The question is as follows:
Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.
@prevNum = ..
is non-standard SQL (i.e. invalid for most DBMS). And what exactly is the "leetcode SQL problem"?