主页 / dba / 问题 / 48594
Accepted
Luigi T.
Asked: 2013-08-24 03:12:15 +0800 CST

查找列中未使用的数字

  • 772

如何在充满数字的列中找到未使用的数字。

表_A：

'id' int(11) unsigned NOT NULL AUTO_INCREMENT,        
'client_code' int(4) unsigned NOT NULL,    
'client_name' varchar(50) NOT NULL,    
PRIMARY KEY ('id')


INSERT INTO Table_A ('client_code','client_name') VALUES (1,'Bob');    
INSERT INTO Table_A ('client_code','client_name') VALUES (2,'Anna');    
INSERT INTO Table_A ('client_code','client_name') VALUES (5,'Jim');    
INSERT INTO Table_A ('client_code','client_name') VALUES (6,'Tom');    
INSERT INTO Table_A ('client_code','client_name') VALUES (10,'Mattew');

如果我SELECT 'client_code' FROM Table_A输出将是1,2,5,6,10. 我怎样才能拥有未使用的号码？ 

(3,4,7,8,9,11,12...)
mysql gaps-and-islands

3 个回答

  1. Best Answer

    要查看缺少的内容，您可以与完整列表进行比较。MySQL
    中没有行生成器（如generate_series()Postgres 中），但各种代理项都在浮动。就像 MySQL 论坛中的这个。

    帮助表提供来自0-9：

    CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

    在此基础上，生成完整的数字范围：

    SELECT i1.i * 10 + i0.i AS client_code
FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max

    现在您可以完全按照要求生成缺失号码列表：

    SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
WHERE  a.client_code IS NULL;

    关键元素是LEFT JOIN和聚合函数GROUP_CONCAT()。
    要获得包括缺失代码在内的完整代码/名称列表，这可能是您的最终目标：

    SELECT n.client_code, a.client_name
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
ORDER  BY n.client_code;

    -> SQLfiddle 演示。

    • 2

  2. 创建一个存储过程如下

    DELIMITER $$
CREATE PROCEDURE CheckNotUsedNos(in v1 int)
BEGIN
DROP TEMPORARY TABLE IF EXISTS `incr`;
CREATE TEMPORARY TABLE `incr` (
  `Id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`Id`)
);
  WHILE v1 > 0 DO
    INSERT incr VALUES (NULL);
    SET v1 = v1 - 1;
  END WHILE;

Select Id from `incr` 
where Id not in 
(select client_code from `Client`);

END $$
DELIMITER;

    根据需要调用参数。（只是尝试 11 ）

    call CheckNotUsedNos(11);

    我得到了以下结果

    ID
3
4
7
8
9
11

    样品小提琴

    • 0

  3. 您可以通过为 and 中的所有行生成的“rownumber”之间的差异来获得Table_A它client_code：

    select * from (
  SELECT @ROWNUMBER:=@ROWNUMBER+1 AS COL FROM Table_A
  JOIN    (SELECT @ROWNUMBER := 0) D) B
where B.COL not in (select client_code from Table_A);

    您的client_code值必须从 1 开始并以 1 递增。

    • -3

相关问题