您可以使用GROUP BY和HAVING并使用条件聚合来查找具有正确检验和状态的批次及其检验计数：

SELECT lot_id,
       COUNT(*) AS num_inspections
FROM   table_name
GROUP BY lot_id
HAVING COUNT(CASE WHEN inspection_id = 49 AND status = 1 THEN 1 END) > 0

如果要查找检验数为49且状态为1的批次，且检验数少于12：

SELECT lot_id,
       COUNT(*) AS num_inspections
FROM   table_name
GROUP BY lot_id
HAVING COUNT(CASE WHEN inspection_id = 49 AND status = 1 THEN 1 END) > 0
AND    COUNT(*) < 12

如果您想检查是否缺少特定检查，则：

SELECT lot_id,
       COUNT(*) AS num_inspections
FROM   table_name
GROUP BY lot_id
HAVING COUNT(CASE WHEN inspection_id = 49 AND status = 1 THEN 1 END) > 0
AND    COUNT(CASE WHEN inspection_id = 35 THEN 1 END) = 0

交叉连接检查与已完成的检查。然后使用NOT EXISTS查找表中缺少的检查。

with
  completed as
  (
    select lot_id, microchip_serial
    from lot_inspection
    where inspection_id = 49 and status = 1
  )
select c.lot_id, c.microchip_serial, i.inspection_id
from completed c
cross join inspection i
where not exists
(
  select null
  from lot_inspection li 
  where li.lot_id = c.lot_id 
  and li.microchip_serial = c.microchip_serial
  and li.inspection_id = i.inspection_id
)
order by c.lot_id, c.microchip_serial, i.inspection_id;

我希望有一个检查表，因为您有一个名为inspection_id的列。如果没有，则在数据库中创建一个one-the-fly或。如果存在检查表，但它包含其他检查ID，则使用另一个CTE仅选择所需的ID。