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.
Best Answer
PROPOSED SOLUTION
Using the IF() function to check for consecutive num values
YOUR SAMPLE DATA
YOUR SAMPLE DATA LOADED
PROPOSED SOLUTION EXECUTED
GIVE IT A TRY !!!