我有以下两个问题：
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" |
+--------+----------+
| ... | ... |
+--------+----------+
1条答案
按热度按时间gudnpqoy1#
您可以尝试这样做（按tailnum连接飞机和航班），按制造商对tailnum和航班进行计数分组，并通过启用子句进行过滤
COUNT(tailnum).