mysql 返回重复项并排除最低 ID

Question

我正在尝试列出查询中的所有行，这些行将返回所有相邻的重复项，这样我就可以获取它们的 id，但我也想从结果中排除编号最小的 id。我怎样才能用我的查询来做到这一点。

我的查询

SELECT 
   a.tail_number,
   min(a.id),
   b.aircraft_id
from aircraft a
left join jobs b on a.id = b.aircraft_id
where
   a.active = 1 and b.aircraft_id is null
   group by a.tail_number having count(*) > 1

当前输出

tail_number min(a.id)   aircraft_id tail_count
125TH       4429        NULL        7
362FX       4223        NULL        7
439FL       4221        NULL        7
453FX       4220        NULL        7
455FX       4259        NULL        7

我试图实现的输出

tail_number min(a.id)   aircraft_id tail_count
125TH       4429        NULL        1
125TH       4430        NULL        1
125TH       4431        NULL        1
125TH       4432        NULL        1
362FX       4223        NULL        1
362FX       4224        NULL        1
362FX       4225        NULL        1
362FX       4226        NULL        1

Answer 1

与获取每个尾号的最低 ID 的子查询连接，然后在 ON 条件下将其从结果中排除。

SELECT a.tail_number, a.id
FROM aircraft AS a
JOIN (SELECT tail_number, MIN(id) AS minid
      FROM aircraft
      WHERE active = 1
      GROUP BY tail_number
      HAVING COUNT(*) > 1) AS m ON a.tail_number = m.tail_number AND a.id != m.minid
LEFT JOIN jobs AS j ON a.id = j.aircraft_id
WHERE j.aircraft_id IS NULL
ORDER BY a.tail_number, a.id

我也将对 active = 1 和 COUNT(*) > 1 的检查移至子查询中，因为主查询中不再有任何分组.