PHP MySQL 在列中找到最小的缺失数

Question

我需要在 MySQL 中读取一个带有 INT Order 的列，并从该列中获取缺少的较低数字:

+--------+---------+
| ID     | Order   |
+--------+---------+
| 1      | 1       |
| 3      | 5       |
| 4      | 3       |
| 5      | 4       |
| 6      | 2       |
| 7      | 6       |
| 8      | 11      |
+--------+---------+

我需要的结果是数字 7，因为 1 到 6 存在并且其他缺失数字大于 7。

$stmtpre    =   "SELECT Order FROM tabla ORDER BY Order DESC";
$data       =   $this   ->  DBMANAGER   ->  BDquery($stmtpre);
        $count      =   0;
        while ($row =   mysqli_fetch_assoc($data)){
            $count++;
            if($row['Order']!==$count){
                $result= $count; #store first lower get
                break;
            }
        }
return $result;

Answer 1

如果 Order 列被索引，您可以使用 SQL 获取第一个缺失的数字，而无需使用排除 LEFT JOIN 读取整个表:

SELECT t1.`Order` + 1 AS firstMissingOrder
FROM tabla t1
LEFT JOIN tabla t2 ON t2.`Order` = t1.`Order` + 1
WHERE t2.`Order` IS NULL
  AND t1.`Order` <> (SELECT MAX(`Order`) FROM tabla)
ORDER BY t1.`Order`
LIMIT 1

或(可能更直观)

SELECT t1.`Order` + 1 AS firstMissingOrder
FROM tabla t1
WHERE NOT EXISTS (
    SELECT 1
    FROM tabla t2
    WHERE t2.`Order` = t1.`Order` + 1
) 
    AND t1.`Order` <> (SELECT MAX(`Order`) FROM tabla)
ORDER BY t1.`Order`
LIMIT 1

第二个查询将由 MySQL 转换为第一个查询。所以它们实际上是相等的。

更新

草莓提到了一个好点:第一个缺失的数字可能是1，这在我的查询中没有涵盖。但我找不到既优雅又快速的解决方案。

我们可以反其道而行之，搜索间隔后的第一个数字。但是需要再次加入表格以找到该差距之前的最后一个现有数字。

SELECT IFNULL(MAX(t3.`Order`) + 1, 1) AS firstMissingOrder
FROM tabla t1
LEFT JOIN tabla t2 ON t2.`Order` = t1.`Order` - 1
LEFT JOIN tabla t3 ON t3.`Order` < t1.`Order`
WHERE t1.`Order` <> 1
  AND t2.`Order` IS NULL
GROUP BY t1.`Order`
ORDER BY t1.`Order`
LIMIT 1

MySQL(在我的例子中是 MariaDB 10.0.19)无法正确优化该查询。在索引 (PK) 1M 行表上大约需要一秒钟，即使第一个缺失的数字是 9。我希望服务器在 t1.Order=10 之后停止搜索，但它没有接缝这样做。

另一种快速但看起来很丑陋(恕我直言)的方法是仅当 Order=1 存在时才在子选择中使用原始查询。否则返回 1。

SELECT CASE
    WHEN NOT EXISTS (SELECT 1 FROM tabla WHERE `Order` = 1) THEN 1
    ELSE (
        SELECT t1.`Order` + 1 AS firstMissingOrder
        FROM tabla t1   
        LEFT JOIN tabla t2 ON t2.`Order` = t1.`Order` + 1
        WHERE t2.`Order` IS NULL
          AND t1.`Order` <> (SELECT MAX(`Order`) FROM tabla)
        ORDER BY t1.`Order`
        LIMIT 1
    )
END AS firstMissingOrder

或者使用UNION

SELECT 1 AS firstMissingOrder FROM (SELECT 1) dummy WHERE NOT EXISTS (SELECT 1 FROM tabla WHERE `Order` = 1)
UNION ALL
SELECT firstMissingOrder FROM (
    SELECT t1.`Order` + 1 AS firstMissingOrder
    FROM tabla t1
    LEFT JOIN tabla t2 ON t2.`Order` = t1.`Order` + 1
    WHERE t2.`Order` IS NULL
      AND t1.`Order` <> (SELECT MAX(`Order`) FROM tabla)
    ORDER BY t1.`Order`
    LIMIT 1
) sub
LIMIT 1