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
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?
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.