mysql - MySQL：在 LEFT JOIN 中添加 WHERE

Question

我有下表：

SequenceNumber 始终是 10 的倍数。对于特定的，我想获得cpId最小的空闲序列号（仍然是 10 的倍数）。例如，对于cpId = 1，可用的最小应该是20。对于cpId = 2，应该是10。

我有以下语句来获取所有 cpId 的最小可用序列号，但我不知道如何WHERE cpId = x在语句中添加一个：

SELECT MIN(t1.sequenceNumber + 10) AS nextID
FROM LogicalConnection t1
   LEFT JOIN LogicalConnection t2
       ON t1.sequenceNumber + 10 = t2.sequenceNumber
WHERE t2.sequenceNumber IS NULL;

DB小提琴：https ://www.db-fiddle.com/f/ag67AkFzfwPZEva8bTN7Q3/2#&togetherjs=L9nHb3Uu7O

谢谢您的帮助！

标签： mysqlsqlleft-join

Answer 1

您可以使用lead()获取下一个数字，然后是一些简单的逻辑：

select cpid,
       (case when min_sn > 10 then 10
             else min(sequenceNumber) + 10
        end)
from (select t.*,
             min(sequenceNumber) over (partition by cpid) as min_sn,
             lead(sequenceNumber) over (partition by cpid order by sequenceNumber) as next_sn
      from t
     ) t
where next_sn is null or next_sn <> sequenceNumber + 10
group by cpid, min_sn;