我有以下两个查询：
Return the number of flights for each manufacturer:
SELECT COUNT(flight) AS num_flights, manufacturer
FROM flights, planes
WHERE flights.tailnum = planes.tailnum
GROUP BY manufacturer
ORDER BY num_flights DESC
这将返回(此处并非所有结果都可见，总共 27 行(：
-------------+---------------------------------+--+--+--+
| num_flights | manufacturer | | | |
+-------------+---------------------------------+--+--+--+
| 67623 | "BOEING" | | | |
+-------------+---------------------------------+--+--+--+
| 36244 | "AIRBUS INDUSTRIE" | | | |
+-------------+---------------------------------+--+--+--+
| 11676 | "AIRBUS" | | | |
+-------------+---------------------------------+--+--+--+
| 8932 | "MCDONNELL DOUGLAS AIRCRAFT CO" | | | |
+-------------+---------------------------------+--+--+--+
| 4856 | "EMBRAER" | | | |
+-------------+---------------------------------+--+--+--+
| 3998 | "MCDONNELL DOUGLAS" | | | |
+-------------+---------------------------------+--+--+--+
| 1259 | "MCDONNELL DOUGLAS CORPORATION" | | | |
+-------------+---------------------------------+--+--+--+
| 247 | "CESSNA" | | | |
+-------------+---------------------------------+--+--+--+
| 162 | "PIPER" | | | |
+-------------+---------------------------------+--+--+--+
| 65 | "BELL" | | | |
+-------------+---------------------------------+--+--+--+
| 63 | "DEHAVILLAND" | | | |
+-------------+---------------------------------+--+--+--+
| 63 | "FRIEDEMANN JON" | | | |
+-------------+---------------------------------+--+--+--+
| 55 | "STEWART MACO" | | | |
+-------------+---------------------------------+--+--+--+
| 54 | "LAMBERT RICHARD" | | | |
+-------------+---------------------------------+--+--+--+
| 51 | "KILDALL GARY" | | | |
+-------------+---------------------------------+--+--+--+
| 47 | "BEECH" | | | |
+-------------+---------------------------------+--+--+--+
| 44 | "MARZ BARRY" | | | |
+-------------+---------------------------------+--+--+--+
| 42 | "AMERICAN AIRCRAFT INC" | | | |
+-------------+---------------------------------+--+--+--+
| 40 | "LEBLANC GLENN T" | | | |
+-------------+---------------------------------+--+--+--+
| 32 | "AGUSTA SPA" | | | |
+-------------+---------------------------------+--+--+--+
| 27 | "SIKORSKY" | | | |
+-------------+---------------------------------+--+--+--+
| 25 | "PAIR MIKE E" | | | |
+-------------+---------------------------------+--+--+--+
| 22 | "DOUGLAS" | | | |
+-------------+---------------------------------+--+--+--+
| 19 | "LEARJET INC" | | | |
+-------------+---------------------------------+--+--+--+
| 18 | "AVIAT AIRCRAFT INC" | | | |
+-------------+---------------------------------+--+--+--+
| 17 | "HURLEY JAMES LARRY" | | | |
+-------------+---------------------------------+--+--+--+
| 13 | "GULFSTREAM AEROSPACE" | | | |
+-------------+---------------------------------+--+--+--+
还有一个：
Return manufacturers with more than 200 planes:
SELECT COUNT(tailnum) AS num_planes, manufacturer
FROM planes
GROUP BY manufacturer
HAVING COUNT(*) > 200
ORDER BY num_planes DESC
返回：
+------------+--------------------+
| num_planes | manufacturer |
+------------+--------------------+
| 1630 | "BOEING" |
+------------+--------------------+
| 400 | "AIRBUS INDUSTRIE" |
+------------+--------------------+
| 368 | "BOMBARDIER INC" |
+------------+--------------------+
| 336 | "AIRBUS" |
+------------+--------------------+
| 299 | "EMBRAER" |
+------------+--------------------+
现在，我正在尝试查询拥有200架飞机以上的每个制造商的航班数量。
编写了以下查询：
SELECT COUNT(flight) AS num_flights, pl.manufacturer
FROM flights fl, planes pl JOIN
(SELECT COUNT(tailnum) AS num_planes, pl2.manufacturer
FROM planes pl2
GROUP BY pl2.manufacturer
HAVING COUNT(*) > 200
ORDER BY num_planes DESC) tm
ON pl.manufacturer = tm.manufacturer
GROUP BY pl.manufacturer
ORDER BY num_flights DESC
但是，此查询返回不正确的航班数，并且需要很长时间才能执行：
+-------------+--------------------+
| num_flights | manufacturer |
+-------------+--------------------+
| 262029020 | "BOEING" |
+-------------+--------------------+
| 64301600 | "AIRBUS INDUSTRIE" |
+-------------+--------------------+
| 59157472 | "BOMBARDIER INC" |
+-------------+--------------------+
| 54013344 | "AIRBUS" |
+-------------+--------------------+
| 48065446 | "EMBRAER" |
+-------------+--------------------+
我在这里做错了什么？
表结构：
planes:
CREATE TABLE planes
(
tailnum VARCHAR(6),
manufacturer VARCHAR(50)
)
+----------+--------------------+
| tailnum | manufacturer |
+----------+--------------------+
| "N10156" | "EMBRAER" |
+----------+--------------------+
| "N102UW" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N103US" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N104UW" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N10575" | "EMBRAER" |
+----------+--------------------+
| "N105UW" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| "N107US" | "AIRBUS INDUSTRIE" |
+----------+--------------------+
| ... | ... |
+----------+--------------------+
flights:
CREATE TABLE flights
(
flight INT,
tailnum VARCHAR(6)
)
+--------+----------+
| flight | tailnum |
+--------+----------+
| 1545 | "N14228" |
+--------+----------+
| 1714 | "N24211" |
+--------+----------+
| 1141 | "N619AA" |
+--------+----------+
| 461 | "N668DN" |
+--------+----------+
| 1696 | "N39463" |
+--------+----------+
| ... | ... |
+--------+----------+
您可以尝试这样做(按尾号连接飞机和航班(，按制造商计算尾号和航班分组，并通过在COUNT(tailnum)上设置子句进行过滤。
SELECT manufacturer AS "Manufacturer",
COUNT(DISTINCT tailnum) AS "Number of planes",
COUNT(flights) as "Number of flights",
FROM planes
INNER JOIN flights
ON (planes.tailnum=flights.tailnum)
GROUP BY manufacturer
HAVING COUNT(DISTINCT tailnum)>200
ORDER BY 3