Consecutive Numbers, consecutivenumbers

Consecutive Numbers, consecutivenumbers

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. solution 1: Refer to [Rank Scores] (http://blog.csdn.net/havedream_one/article/details/453950 63) Align the ranking from top to bottom. IF the ranking is equal, the ranking is equal. IF the ranking is not equal, add the following code: select num, @ curRank: = @ curRank + IF (@ preNum = num, 0, 1) as rank, @ preNum: = num from logs, (select @ curRank: = 0) r, (select @ preNum: = null) p; + ------ + -------------- + | num | rank | @ preNum: = num | + ------ + -------------- + | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 1 | 3 | 1 | 2 | 4 | 2 | 2 | 4 | 2 | + ------ + ------------ + As shown above, if the continuous ranking of a num exceeds 3, the question is correct. Select num, count (rank) as count from (select num, @ curRank: = @ curRank + IF (@ preNum = num, 0, 1) as rank, @ preNum: = num from logs s, (select @ curRank: = 0) r, (select @ preNum: = null) p) t group by rank; + ------ + ------- + | num | count | + ------ + ------- + | 1 | 3 | 2 | 1 | 1 | 1 | 2 | 2 | + ------ + ------- + after obtaining the preceding table, then use the having condition to select. Final Result: select distinct num from (select num, count (rank) as count from (select num, @ curRank: = @ curRank + IF (@ preNum = num, 0, 1) as rank, @ preNum: = num from logs s, (select @ curRank: = 0) r, (select @ preNum: = null) p) t group by rank having count> = 3;) tmp; solution 2: 1. Search from the first record, same before and after, count ++, not equal, count = 1; 2. Judgment: IF rank is greater than 3, select distinct num from (select num, @ curRank: = @ preRank + IF (@ preNum = num, 1, 0), @ preRank: = IF (@ preNum = num, @ curRank, 1) as rank, @ preNum: = num from Logs s, (select @ preRank: = 1) r, (select @ preNum: = null) p) t where rank> = 3;