mysql - 如何取消MySQL中having子句的组效应？

Question

查询:对于每个获得超过三架飞机认证的飞行员，查找他/她获得认证的每架飞机的 eid 和最大巡航范围。

我写的查询:

select certified.eid,cruising_range
from certified join employee 
on employee.eid=certified.eid
join aircraft
on certified.aid=aircraft.aid
having count(certified.eid)>3;

输出:

它仅显示一个巡航范围，但id 10的飞行员拥有4架飞机的认证。如何获取其他行？

Answer 1

您的问题实际上分为两部分:

1. 查找获得三架以上飞机认证的飞行员

   SELECT eid FROM certified GROUP BY eid HAVING COUNT(*) > 3
   

2. 查找这些飞机的巡航范围

   SELECT eid, cruising_range FROM aircraft JOIN certified USING (aid) ...
   

将两者结合起来:

SELECT eid, cruising_range FROM aircraft JOIN certified USING (aid) JOIN (
  SELECT eid FROM certified GROUP BY eid HAVING COUNT(*) > 3
) t USING (eid)