【数据库-MySql】如何用查询语句查找出指定ID字段缺失的ID号

问 

如何写select语句列出数据库中某ID范围段，如001-100之间缺乏的数字？
如何写select语句列出数据库中某ID范围段，如1-100之间缺乏的数字？
如何写select语句列出数据库中某ID范围段，如1-1000之间缺乏的数字？
如1-1000之间缺少的数字？
如1-1000之间缺失的数字？
表数据：
id
001
002
004
005
006
008
010
...

要求结果：
003
007
009
...

一、只缺少一条记录或缺失的记录不连续时

原理：将数据通过ID进行两两分组，分组规则 ID除以2 取整，整数一样的分为一组，如果该组的记录数量少于2，则表示缺1条记录，根据该条记录的ID与2求余，计算出缺失的ID数。

SELECT
CASE
 WHEN id1 MOD 2 = 0 THEN id1 - 1
 ELSE	id1 +1
END AS '缺失ID'
FROM	table1
GROUP BY CEIL(id1/2)	
HAVING COUNT(*) < 2;

 

 原理：将每条记录的ID+1 去原表查询，如果不存在则输出 该“ID+10001”的字符串，并去掉字符串的第一位，得到缺失的记录ID。

缺点就是缺失的记录不能是连续的，如果是连续的记录缺失，用下面的语句则会出现查询缺失记录丢失的情况。

select right(id1+10001,LENGTH('10001')-1) 
from table1 a 
where not exists(select 1 from table1 where id1=a.id1+1)
  and a.id1<(select max(id1) from table1)

 

二、缺少少量数据时

原理：先用最大的ID号减去总的记录条数，获取缺失的记录条数，按“缺失的记录条数+1”,作为分组大小CNT，如果当前组的数量小于CNT,那么则显示以下信息：

1、当前段起始编号

2、当前段结束编号

3、每段总个数

4、当前段缺失ID个数

5、当前段拥有的编号

缺点：不能直接得出缺失的ID号，需要肉眼观察，建议在缺失量小于10的时候使用。

SELECT
 CEIL(id1 / X.cnt) * X.cnt - X.cnt + 1 AS '当前段起始编号',
 (CEIL(id1 / X.cnt)) * X.cnt AS '当前段结束编号',
 X.cnt AS '每段总个数',
 X.cnt - COUNT(*) AS '当前段缺失ID个数',
 GROUP_CONCAT(DISTINCT id1 ORDER BY id1 ASC SEPARATOR "," ) AS '当前段拥有编号'
FROM table1
JOIN (SELECT max(id1) - COUNT(*) + 1 AS cnt FROM table1) AS X
GROUP BY CEILING(id1/ X.cnt)
HAVING	COUNT(*) < (SELECT max(id1) - COUNT(*) + 1 AS cnt FROM table1 );

 如图所示，红色部分为缺少的编号。

三、适合任何情况

原理：使用存储过程，先获取最大的ID与最小的ID，然后从小到大遍历，把查询结果为null的ID,拼接到结果数据里，最后输出出来。

特点：适合任何条件

缺点：代码量多

DROP PROCEDURE IF EXISTS FindLessId;
delimiter $$
CREATE PROCEDURE FindLessId(IN tbName varchar(100),IN colName varchar(100))     
BEGIN
	DECLARE SQL_TEXT1 VARCHAR(500);
	DECLARE SQL_TEXT2 VARCHAR(500);
	DECLARE SQL_TEXT3 VARCHAR(500);

	DECLARE LESS_ID_ARRAY TEXT;
	DECLARE nStart INT;
  DECLARE nEnd INT;
	DECLARE nId INT;
		

	SET SQL_TEXT1  = CONCAT('SELECT min(',colName,') into @numStart From ',tbName);
  SET SQL_TEXT2  = CONCAT('SELECT max(',colName,') into @numEnd From ',tbName);

  SET @tmp = CONCAT(SQL_TEXT1);
	PREPARE stmt FROM @tmp ;
  EXECUTE stmt;
	SET nStart=  @numStart; 
  DEALLOCATE PREPARE stmt;

  SET @tmp = CONCAT(SQL_TEXT2);
  PREPARE stmt FROM @tmp ;
  EXECUTE stmt;
  SET nEnd=  @numEnd;
  DEALLOCATE PREPARE stmt;

  SET LESS_ID_ARRAY = '';
  IF nStart is not NULL AND nEnd IS NOT NULL THEN 
		WHILE nStart < nEnd DO
			SET SQL_TEXT3 =CONCAT('SELECT ',colName,' into @numId FROM ',tbName,' WHERE ',colName,'=',nStart,' limit 1;');

			SET @tmp = CONCAT(SQL_TEXT3);
			PREPARE stmt FROM @tmp ;
			EXECUTE stmt;
			
			IF (ISNULL(@numId)) THEN
				IF LESS_ID_ARRAY <>  '' THEN
					SET LESS_ID_ARRAY = CONCAT(LESS_ID_ARRAY,',',nStart);
				ELSE
					SET LESS_ID_ARRAY = CONCAT(LESS_ID_ARRAY,nStart);
					END IF;
			END IF;

			DEALLOCATE PREPARE stmt;

			SET @numId = NULL;
			SET nStart= nStart+1;
		END WHILE;
		SELECT LESS_ID_ARRAY;
	end if;
END  $$
delimiter ;

CALL FindLessId("table1","id1");
DROP PROCEDURE IF EXISTS FindLessId;