MYSQL:按不同组查找序列中的缺失值

Question

嘿，请帮忙，我有 12 个项目的完整序列，我通过 2 个不同的组(12345 和 54321)识别这些项目。现在，我需要确定项目“12345”的第一个序列在 4 处停止并在 10 处重新开始。类似这样的事情:

我有这张表:

------------------
|seq  |partNumber|
------------------
| 1   |  12345   |
| 2   |  12345   |
| 3   |  12345   |
| 4   |  12345   |
| 10  |  12345   |
| 11  |  12345   |
| 12  |  12345   |
| 5   |  54321   |
| 6   |  54321   |
| 7   |  54321   |
| 8   |  54321   |
| 9   |  54321   |
------------------

我需要找到这个结果:

------------
|Start|Stop|
------------
|  5  |  9 | (partnumber:12345)
------------

我使用的查询:

select start, stop from (
 select m.partNumber + 1 as start,
    (select min(partNumber) - 1 from seq as x where x.partNumber > m.partNumber) as stop
  from seq as m
    left outer join seq as r on m.partNumber = r.partNumber - 1 where r.partNumber is null) as x
where stop is not null;

但是，这个查询给了我这个结果:

------------
|Start|Stop|
------------
|  9  |  9 | (partnumber:12345)
------------

最终结果:我想确定“12345”的序列从 1 开始到 4 结束(中断)在 10 结束 12 处重新开始，我在 5 到 9 之间有一个间隙。“54321”的另一个序列从 5 开始到 9 结束，这里我不'有差距。

Answer 1

对于结构化查询语言的结构化部分来说，这是一个很好的应用程序。

我想您的 seq 值永远不会小于零或大于任意值 15624。这个猜测很重要:我们需要一个包含该范围内所有基数的表格，才能进行缺失数字检测。

有这样一张表

SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
  JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F

(如果您使用的是 MariaDB，则可以使用 Sequence 表 seq_0_to_15624 代替这一 block SQL 代码。)

接下来，您需要一种方法来找出每个零件号的 seq 的最低值和最高值。你这样做。

       SELECT partNumber, MIN(seq) minSeq, MAX(seq) maxSeq
         FROM seq
        GROUP BY partNumber

接下来，您需要生成一个表格，显示每个零件号从最小到最大的所有可能序列号:

SELECT cardinals.seq, r.partNumber
  FROM (
        SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq  
          FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
          JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
       ) cardinals
  JOIN (
         SELECT partNumber, MIN(seq) minSeq, MAX(seq) maxSeq
           FROM seq
          GROUP BY partNumber
   ) r ON cardinals.seq >= r.minSeq AND cardinals.seq <= r.maxSeq

最后，您可以 LEFT JOIN 它到您的原始表并执行 WHERE val IS NULL 以找到您丢失的序列号。

SELECT cardinals.seq, r.partNumber
  FROM (
        SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq  
          FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
          JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
         JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
           ) cardinals
  JOIN (
             SELECT partNumber, MIN(seq) minSeq, MAX(seq) maxSeq
               FROM seq
              GROUP BY partNumber
       ) r ON cardinals.seq >= r.minSeq AND cardinals.seq <= r.maxSeq
  LEFT JOIN seq ON cardinals.seq = seq.seq AND r.PartNumber = seq.partNumber
 WHERE seq.seq IS NULL