· 7 years ago · Nov 26, 2018, 11:24 AM
1SELECT Num FROM Logs GROUP BY Num HAVING COUNT (Num) >1
2
3mysql> DROP DATABASE IF EXISTS guru;
4Query OK, 0 rows affected, 1 warning (0.00 sec)
5
6mysql> CREATE DATABASE guru;
7Query OK, 1 row affected (0.00 sec)
8
9mysql> USE guru
10Database changed
11mysql> CREATE TABLE logs
12 -> (id int not null auto_increment,
13 -> num int not null,
14 -> primary key (id));
15Query OK, 0 rows affected (0.05 sec)
16
17mysql> INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
18Query OK, 7 rows affected (0.00 sec)
19Records: 7 Duplicates: 0 Warnings: 0
20
21mysql> SELECT * FROM logs;
22+----+-----+
23| id | num |
24+----+-----+
25| 1 | 1 |
26| 2 | 1 |
27| 3 | 1 |
28| 4 | 2 |
29| 5 | 1 |
30| 6 | 2 |
31| 7 | 2 |
32+----+-----+
337 rows in set (0.00 sec)
34
35mysql> SELECT Num FROM Logs GROUP BY Num HAVING COUNT(Num) > 1;
36+-----+
37| Num |
38+-----+
39| 1 |
40| 2 |
41+-----+
422 rows in set (0.00 sec)
43
44mysql>
45
46Write a SQL query to find all numbers that appear at least three times consecutively
47
48SET @curnum = -999;
49SET @same = 1;
50SET @group = 0;
51SELECT DISTINCT num FROM
52(
53 SELECT grp,num,MAX(samecount) same FROM
54 (
55 SELECT
56 (@group := @group + IF(@curnum=num,0,1)) grp,
57 (@same := IF(@curnum=num,@same+1,1)) samecount,
58 (@curnum:=num),num
59 FROM logs ORDER BY id
60 ) AA GROUP BY grp,num HAVING same >= 3
61) A;
62
63mysql> SET @curnum = -999;
64Query OK, 0 rows affected (0.00 sec)
65
66mysql> SET @same = 1;
67Query OK, 0 rows affected (0.00 sec)
68
69mysql> SET @group = 0;
70Query OK, 0 rows affected (0.00 sec)
71
72mysql> SELECT DISTINCT num FROM
73 -> (
74 -> SELECT grp,num,MAX(samecount) same FROM
75 -> (
76 -> SELECT
77 -> (@group := @group + IF(@curnum=num,0,1)) grp,
78 -> (@same := IF(@curnum=num,@same+1,1)) samecount,
79 -> (@curnum:=num),num
80 -> FROM logs ORDER BY id
81 -> ) AA GROUP BY grp,num HAVING same >= 3
82 -> ) A;
83+-----+
84| num |
85+-----+
86| 1 |
87+-----+
881 row in set (0.01 sec)
89
90mysql>
91
92SET @curnum = -999;
93SET @same = 1;
94SET @group = 0;
95SELECT
96 (@group := @group + IF(@curnum=num,0,1)) grp,
97 (@same := IF(@curnum=num,@same+1,1)) samecount,
98 (@curnum:=num),num
99FROM logs ORDER BY id;
100
101mysql> SET @curnum = -999;
102Query OK, 0 rows affected (0.00 sec)
103
104mysql> SET @same = 1;
105Query OK, 0 rows affected (0.00 sec)
106
107mysql> SET @group = 0;
108Query OK, 0 rows affected (0.00 sec)
109
110mysql> SELECT
111 -> (@group := @group + IF(@curnum=num,0,1)) grp,
112 -> (@same := IF(@curnum=num,@same+1,1)) samecount,
113 -> (@curnum:=num),num
114 -> FROM logs ORDER BY id
115 -> ;
116+------+-----------+----------------+-----+
117| grp | samecount | (@curnum:=num) | num |
118+------+-----------+----------------+-----+
119| 1 | 1 | 1 | 1 |
120| 1 | 2 | 1 | 1 |
121| 1 | 3 | 1 | 1 |
122| 2 | 1 | 2 | 2 |
123| 3 | 1 | 1 | 1 |
124| 4 | 1 | 2 | 2 |
125| 4 | 2 | 2 | 2 |
126+------+-----------+----------------+-----+
1277 rows in set (0.00 sec)
128
129mysql>
130
131mysql> SET @curnum = -999;
132Query OK, 0 rows affected (0.00 sec)
133
134mysql> SET @same = 1;
135Query OK, 0 rows affected (0.00 sec)
136
137mysql> SET @group = 0;
138Query OK, 0 rows affected (0.00 sec)
139
140mysql> SELECT grp,num,MAX(samecount) same FROM
141 -> (
142 -> SELECT
143 -> (@group := @group + IF(@curnum=num,0,1)) grp,
144 -> (@same := IF(@curnum=num,@same+1,1)) samecount,
145 -> (@curnum:=num),num
146 -> FROM logs ORDER BY id
147 -> ) AA GROUP BY grp,num HAVING same >= 3;
148+------+-----+------+
149| grp | num | same |
150+------+-----+------+
151| 1 | 1 | 3 |
152+------+-----+------+
1531 row in set (0.00 sec)
154
155mysql>
156
157INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
158INSERT INTO logs (num) VALUES (7),(7),(7),(8),(8),(9),(5);
159INSERT INTO logs (num) VALUES (5),(6),(11),(11),(11),(2),(2);
160INSERT INTO logs (num) VALUES (4),(4),(4),(4),(4),(3),(3);
161INSERT INTO logs (num) VALUES (1),(1),(1),(2),(1),(2),(2);
162
163mysql> SET @curnum = -999;
164Query OK, 0 rows affected (0.00 sec)
165
166mysql> SET @same = 1;
167Query OK, 0 rows affected (0.00 sec)
168
169mysql> SET @group = 0;
170Query OK, 0 rows affected (0.00 sec)
171
172mysql> SELECT DISTINCT num FROM
173 -> (
174 -> SELECT grp,num,MAX(samecount) same FROM
175 -> (
176 -> SELECT
177 -> (@group := @group + IF(@curnum=num,0,1)) grp,
178 -> (@same := IF(@curnum=num,@same+1,1)) samecount,
179 -> (@curnum:=num),num
180 -> FROM logs ORDER BY id
181 -> ) AA GROUP BY grp,num HAVING same >= 3
182 -> ) A;
183+-----+
184| num |
185+-----+
186| 1 |
187| 7 |
188| 11 |
189| 4 |
190+-----+
1914 rows in set (0.00 sec)
192
193mysql>
194
195Declare @tbl Table
196(
197 Id int,
198 Num int
199)
200
201Insert into @tbl
202 select '1','1'
203union all select '2','1'
204union all select '3','1'
205union all select '4','2'
206union all select '5','1'
207union all select '6','2'
208union all select '7','2'
209select * from @tbl
210
211DECLARE @cnt INT
212SET @cnt = 3
213
214SELECT
215 Id, Num
216FROM
217(
218 SELECT
219 Id, Num, groupID,
220 COUNT(*) OVER (PARTITION BY Num, groupID) AS groupCnt
221 FROM
222 (
223 SELECT
224 Id, Num,
225 ROW_NUMBER() OVER ( ORDER BY Id)
226 - ROW_NUMBER() OVER (PARTITION BY Num ORDER BY Id) AS groupID
227 FROM
228 @tbl
229 ) T1
230) T2
231WHERE
232 groupCnt >= @cnt
233ORDER BY
234 Id
235
236SELECT DISTINCT l1.Num As ConsecutiveNums
237FROM Logs l1, Logs l2, Logs l3
238WHERE l1.Num = l2.Num
239 AND l2.Num = l3.Num
240 AND l1.Id = l2.Id + 1
241 AND l2.Id = l3.Id + 1;
242
243SELECT DISTINCT
244 l1.Num AS ConsecutiveNums
245FROM
246 Logs l1,
247 Logs l2,
248 Logs l3
249WHERE
250 l1.Id = l2.Id - 1
251 AND l2.Id = l3.Id - 1
252 AND l1.Num = l2.Num
253 AND l2.Num = l3.Num
254
255select distinct(a.Num) as ConsecutiveNums
256from Logs a, Logs b,Logs c
257where a.Id=b.Id+1 and a.Num=b.Num
258and b.Id=c.Id+1 and b.Num=c.Num