· 8 years ago · Dec 08, 2017, 05:36 AM
1ID | Value | More fields...
2---------------------------------
3 2 | Cat | ...
4 3 | Fish | ...
5 6 | Dog | ...
6 7 | Aardvark | ...
7 9 | Owl | ...
8 10 | Pig | ...
9 11 | Badger | ...
10 15 | Mongoose | ...
11 19 | Ferret | ...
12
13ID
14----
15 1
16 4
17 5
18 8
19 12
20 13
21 14
22 16
23 17
24 18
25
26declare @id int
27declare @maxid int
28
29set @id = 1
30select @maxid = max(id) from tbl
31
32create temporary table IDSeq
33(
34 id int
35)
36
37while @id < @maxid
38begin
39 insert into IDSeq values(@id)
40
41 set @id = @id + 1
42end
43
44select
45 s.id
46from
47 idseq s
48 left join tbl t on
49 s.id = t.id
50 where t.id is null
51
52 drop table IDSeq
53
54declare @id int
55declare @maxid int
56
57set @id = 1
58select @maxid = max(id) from tbl
59
60create table #IDSeq
61(
62 id int
63)
64
65while @id < @maxid --whatever you max is
66begin
67 insert into #IDSeq values(@id)
68
69 set @id = @id + 1
70end
71
72select
73 s.id
74from
75 #idseq s
76 left join tbl t on
77 s.id = t.id
78 where t.id is null
79
80 drop table #IDSeq
81
82;WITH Missing (missnum, maxid)
83AS
84(
85 SELECT 1 AS missnum, (select max(id) from @TT)
86 UNION ALL
87 SELECT missnum + 1, maxid FROM Missing
88 WHERE missnum < maxid
89)
90SELECT missnum
91FROM Missing
92LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
93WHERE tt.id is NULL
94OPTION (MAXRECURSION 0);
95
96DECLARE @TEST_ID integer, @LAST_ID integer, @ID integer
97
98SET @TEST_ID = 1 -- start compare with this ID
99SET @LAST_ID = 100 -- end compare with this ID
100
101WHILE @TEST_ID <= @LAST_ID
102BEGIN
103 SELECT @ID = (SELECT <column> FROM <table> WHERE <column> = @TEST_ID)
104 IF @ID IS NULL
105 BEGIN
106 PRINT 'Missing ID: ' + CAST(@TEST_ID AS VARCHAR(10))
107 END
108 SET @TEST_ID = @TEST_ID + 1
109END
110
111select level id -- generate 1 .. 19
112from dual
113connect by level <= 19
114
115minus -- remove from that set
116
117select id -- everything that is currently in the
118from table -- actual table
119
120SELECT DISTINCT id +1
121FROM mytable
122WHERE id + 1 NOT IN (SELECT DISTINCT id FROM mytable);
123
124SELECT all_ids AS missing_ids
125FROM generate_series((SELECT MIN(id) FROM your_table), (SELECT MAX(id) FROM your_table)) all_ids
126EXCEPT
127SELECT id FROM your_table
128
129SELECT distinct number
130
131FROM master..spt_values
132
133WHERE number BETWEEN 1 and (SELECT max(id) FROM MyTable)
134
135AND number NOT IN (SELECT id FROM MyTable)
136
137DECLARE @MaxID INT = (SELECT MAX(ID) FROM TABLE1)
138SELECT SeqID AS MissingSeqID
139FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
140LEFT JOIN dbo.TABLE1 t ON t.ID = LkUp.SeqID
141WHERE t.ID is null and SeqID < @MaxID
142
143nohup mysql --password=xx -e 'select id from tablename order by id desc' databasename > /home/ids.txt &
144
145T="$(date +%s)";
146i=1574115;
147while read line; do
148 if [[ "$line" != "$i" ]] ; then
149 if [[ $i -lt 1 ]] ; then break; fi;
150 if [[ $line -gt 1 ]] ; then
151 missingsequenceend=$(( $line + 1 ));
152 minusstr="-";
153 missingsequence="$missingsequenceend$minusstr$i";
154 expectnext=$(( $line - 1 ));
155 i=$expectnext;
156 echo -e "$missingsequence";
157 fi;
158 else
159 i=$(( $i - 1 ));
160 fi;
161done
162< /home/ids.txt;
163T="$(($(date +%s)-T))";
164echo "Time in seconds: ${T}"
165
1661494505-1494507
16747566-47572
168Time in seconds: 57
169
170use dbname;
171drop procedure if exists dorepeat;
172delimiter #
173CREATE PROCEDURE dorepeat()
174BEGIN
175set @id = 1;
176set @maxid = 1573736;
177drop table if exists IDSeq;
178create temporary table IDSeq
179(
180 id int
181);
182
183WHILE @id < @maxid DO
184 insert into IDSeq values(@id);
185 set @id = @id + 1;
186END WHILE;
187
188select
189 s.id
190from
191 IDSeq s
192 left join tablename t on
193 s.id = t.id
194 where t.id is null;
195
196drop table if exists IDSeq;
197
198END#
199delimiter ;
200CALL dorepeat;
201
202SELECT a.id+1 AS start, MIN(b.id) - 1 AS end
203 FROM tablename AS a, tablename AS b
204 WHERE a.id < b.id
205 GROUP BY a.id
206 HAVING start < MIN(b.id)
207
208DELIMITER ||
209DROP PROCEDURE IF EXISTS proc_missing ||
210CREATE PROCEDURE proc_missing()
211BEGIN
212SET @minID = (SELECT MIN(`id`) FROM `tbl_name` WHERE `user_id`=13);
213SET @maxID = (SELECT MAX(`id`) FROM `tbl_name` WHERE `user_id`=13);
214REPEAT
215 SET @tableID = (SELECT `id` FROM `tbl_name` WHERE `id` = @minID);
216 IF (@tableID IS NULL) THEN
217 INSERT INTO temp_missing SET `missing_id` = @tableID;
218 END IF;
219 SET @minID = @minID + 1;
220UNTIL(@minID <= @maxID)
221END REPEAT;
222END ||
223DELIMITER ;
224
225select sno as missing from(SELECT @row := @row + 1 as sno FROM
226(select 0 union all select 1 union all select 3 union all select 4 union all
227select 5 union all select 6 union all select 6 union all select 7 union all
228select 8 union all select 9) t,(select 0 union all select 1 union all select 3
229union all select 4 union all select 5 union all select 6 union all select 6
230union all select 7 union all select 8 union all select 9) t2,(select 0
231union all select 1 union all select 3 union all select 4 union all select 5
232union all select 6 union all select 6 union all select 7 union all select 8
233union all select 9) t3, (select 0 union all select 1 union all select 3 union
234all select 4 union all select 5 union all select 6 union all select 6 union all
235select 7 union all select 8 union all select 9) t4,
236(SELECT @row:=0) as b where @row<1000) as a where a.sno not in
237 (select distinct b.no from
238(select b.*,if(@mn=0,@mn:=b.no,@mn) as min,(@mx:=b.no) as max from
239 (select ID as no from TABLE_NAME as a) as b,
240 (select @mn:=0,@mx:=0) as x order by no) as b) and
241 a.sno between @mn and @mx;
242
243WITH Missing (missnum,maxid) as (
244 SELECT 1 missnum, (select max(id) from :YOURTABLE) maxid from dual
245 UNION ALL
246 SELECT m.missnum + 1,m.maxid
247 FROM Missing m
248 WHERE m.missnum < m.maxid
249)
250SELECT missnum
251FROM Missing
252LEFT OUTER JOIN :YOURTABLE tt on tt.id = Missing.missnum
253WHERE tt.id is NULL
254
255WITH Missing(missnum, maxid) AS
256 (SELECT (SELECT MIN(tmMIN.TETmeetingID)
257 FROM tblTETMeeting AS tmMIN)
258 AS missnum,
259 (SELECT MAX(tmMAX.TETmeetingID)
260 FROM tblTETMeeting AS tmMAX)
261 AS maxid
262 UNION ALL
263 SELECT missnum + 1, maxid
264 FROM Missing
265 WHERE missnum < maxid)
266SELECT missnum AS TETmeetingID,
267 tt.DateID,
268 tt.WeekNo,
269 tt.TETID
270FROM Missing LEFT JOIN tblTETMeeting tt ON tt.TETmeetingID = Missing.missnum
271WHERE tt.TETmeetingID IS NULL
272UNION
273SELECT tt.TETmeetingID,
274 tt.DateID,
275 tt.WeekNo,
276 tt.TETID
277FROM tblTETMeeting AS tt
278OPTION ( MAXRECURSION 0 )
279
280TETmeetingID DateID WeekNo TETID
28129 3063 21 1
28230 null null null
28331 null null null
28432 null null null
28533 null null null
28634 3070 22 1
28735 3073 23 1
288
289SELECT DISTINCT id -1
290FROM users
291WHERE id != 1 AND id - 1 NOT IN (SELECT DISTINCT id FROM users)
292
293select lft.id + 1 as missing_ids
294from tbl as lft left outer join tbl as rght on lft.id + 1 = rght.id
295where rght.id is null and lft.id between 1 and (Select max(id)-1 from tbl)