MySQL在附加条件下找到最小未使用值

Question

我有一个像这样的 tbl MySQL 表:

+----+------+
| id | col2 |
+----+------+
| 1  | AAA  |
| 1  | BBB  |
| 2  | AAA  |
| 3  | AAA  |
| 3  | BBB  |
| 4  | AAA  |
+----+------+

如何为 col2 选择第一个未使用的 ID，其中 col2="BBB"(示例返回 2)。

基本查询是:

SELECT t1.id+1 AS UnusedID 
FROM tbl AS t1 
LEFT JOIN tbl AS t2 ON t1.id+1 = t2.id 
WHERE t2.id IS NULL 
ORDER BY t1.id LIMIT 1;

但是如何添加条件cl2="BBB"呢？

Answer 1

您需要在 ON 子句和 WHERE 子句中添加条件:

SELECT t1.id + 1 AS UnusedID 
FROM tbl t1 LEFT JOIN
     tbl t2
     ON t1.id + 1 = t2.id AND t2.col2 = t1.col2
WHERE t1.col2 = 'BBB' AND t2.id IS NULL 
ORDER BY t1.id
LIMIT 1;