Select DISTINCT l1.Num from Logs l1, Logs l2, Logs l3 where l1.Id=l2.Id-1 and l2.Id=l3.Id-1 and l1.Num=l2.Num and l2.Num=l3.Num
Why do you need 3 Logs?
What's the problem with
SELECT DISTINCT l1.Num FROM Logs l1, Logs l2 WHERE l1.Id+1 = l2.Id AND l1.Id+2 = l2.Id AND l1.Num=l2.Num;
(It doesn't work, but I still want to know why)
Why is it that the above works but this doesn't?
select distinct l1.Num from Logs l1 join Logs l2 on l1.Id = l2.Id - 1 join Logs l3 on l2.Id = l3.Id -1 where lt.Num = l2.Num = l3.Num
Say l1.Id is 2 (making l2.Id = 3), then when this row is selected from l1 table it is locked with that row.
Now, it is impossible to have l1.Id of 1 (since l1.Id+2 = 3) when we have previously selected a row in l1 that corresponds to l1.Id = 2.
lt.Num is replaced with
l1.Num I've never seen a three-way equality operator. I'm no expert in MySQL, but it's usually interpreted like this in other languages:
(l1.Num = l2.Num) = l3.Num, so a boolean expression is compared to
l3.Num meaning it'll only match if
l3.Num is 0 or 1 giving an incorrect solution to this problem.
select Num from Logs group by Num having count(Num) > 3
why this does not work?
We just ned to get the numbers that appear atleast 3 times. logically this should also be true
The question asked for consecutive numbers, so you need to look at the Id column to define what "consecutive" means in SQL
I think three-way equality operator is the problem, if you just them pairwise, the problem will be solved.
select distinct l1.Num from
Logs l1 join Logs l2
on l1.Id = l2.Id - 1
join Logs l3
on l2.Id = l3.Id -1
where l1.Num = l2.Num and l2.Num = l3.Num
My initial try is similar but not as good as this one. However my code works on my local mysql instance, not when I submit.
SELECT DISTINCT t1.Num FROM Logs AS t1 WHERE EXISTS (SELECT 1 FROM Logs AS t2 WHERE t2.Id = t1.Id+1 AND t2.Num = t1.Num) AND EXISTS (SELECT 1 FROM Logs AS t3 WHERE t3.Id = t1.Id+2 AND t3.Num = t1.Num);
It doesnt check for the Num to be equal for the 3rd consecutive row (for which we need another 'Logs l3' such as in your case)
I think the question is not rigorous, the pre-condition is that the id is also consecutive!
@hbli123 you need add
ConsecutiveNums since leetcode has changed the expected name of the output.
Select DISTINCT l1.Num ConsecutiveNums from Logs l1, Logs l2, Logs l3 where l1.Id=l2.Id-1 and l2.Id=l3.Id-1 and l1.Num=l2.Num and l2.Num=l3.Num
@lemonxixi maybe you should update your answer.
Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.