PHP MySQL在列中找到最小的缺失数 [英] PHP MySQL find smallest missing number in column

问题描述

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

I need to read a column with INT Order in MySQL and get from the column the lower number missing:

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

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

The result I need is the number 7, as 1 through 6 exist and other missing numbers are greater than 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;

推荐答案

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

If the Order column is indexed, you could get the first missing number with SQL, without reading the complete table using an excluding 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转换为第一个查询.因此它们实际上是相等的.

The second query will be converted by MySQL to the first one. So they are practicaly equal.

更新

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

Strawberry mentioned a good point: The first missing number might be 1, which is not covered in my query. But i wasn't able to find a solution, which is both - elegant and fast.

我们可以采取相反的方法，并在出现间隔后搜索第一个数字.但是需要再次加入表以查找该间隔之前的最后一个现有数字.

We could go the opposite way and search for the first number after a gap. But would need to join the table again to find the last existing number before that gap.

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)无法正确优化该查询.即使第一个丢失的数字为9，在索引(PK)1M行表上也要花费大约一秒钟的时间.我希望服务器在t1.Order=10之后停止搜索，但是似乎不这样做.

MySQL (in my case MariaDB 10.0.19) is not able to optimize that query properly. It takes about one second on an indexed (PK) 1M row table, even though the first missing number is 9. I would expect the server to stop searching after t1.Order=10, but it seams not to do that.

另一种快速但看起来很丑陋的方法(IMHO)是仅在Order=1存在的情况下才在子选择中使用原始查询.否则返回1.

Another way, which is fast but looks ugly (IMHO), is to use the original query in a subselect only if Order=1 exists. Otherwise return 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

这篇关于PHP MySQL在列中找到最小的缺失数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！