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

Question

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

+--------+---------+
| 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获得第一个缺失的数字，而无需使用排除的左联接读取完整的表：

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行表需要花费大约1秒的时间，尽管第一个丢失的数字是9。我希望服务器停止搜索t1.Order=10，但它不会这样做。

另一种方法是只在存在Order=1的情况下在子subselect中使用原始查询，这种方法速度快但看起来很难看(IMHO)。否则返回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

Answer 2

可能是很远的路，但这里有一个方法：

while ($row = mysqli_fetch_assoc($data)) {
    $orders[] = $row['Order'];
}

$result = min(array_diff(range(min($orders), max($orders)), $orders));

• 创建一个范围，从找到的最低订单到最大订单。
• 计算与找到的订单之间的差异，以获得丢失的订单。
• 从缺货处查找最低订单号。

这假设您希望使用从查询返回的最低和最高数字作为范围。如果您希望总是从1开始，使用1而不是min($orders)。

此外，正如草莓指出的那样，Order是MySQL中的一个保留字，因此可以考虑修改它，或者用倒计时来分隔它，从tabla中选择Order。

Answer 3

来自PHP端：

我更多地研究解决方案：

Fisrt呼叫功能：

$stmtpre    =   "SELECT Order FROM tabla ORDER BY Order ASC";
$data       =   $this   ->  DBMANAGER   ->  BDqueryFirstMissingINT($stmtpre, DATABASE);
echo    $data;

第二

function BDqueryFirstMissingINT($stmtpre,$dbUsing){
    $data       =   $this   ->  BDquery($stmtpre, $dbUsing); #run the query
    $count      =   0;
    while ($row =   mysqli_fetch_array($data)){
        $count++;
        $value  =   (int)$row[0];
        if($value!==$count){
            $result = $count;
            break;
        }
    }
    return $result;
}

谢谢你的帮助