我需要找到航班总数最大的航空公司的名称,街道,城市,州和国家。
下面是我到目前为止的代码:
SELECT MAX(tName) as name, MAX(tStreet) as street, MAX(tCity) as city, MAX(tState) as state,
MAX(tCountry) as country, MAX(totalFlights) as maxTotal
FROM(
SELECT FLIGHT.name as tName, AIRLINE.street as tStreet, AIRLINE.city as tCity, AIRLINE.state as
tState, AIRLINE.country as tCountry, COUNT(flightNumber) as totalFlights
FROM FLIGHT
LEFT JOIN AIRLINE ON AIRLINE.name = FLIGHT.name
GROUP BY FLIGHT.name)as t;
下面是我正在连接的两个表:
CREATE TABLE AIRLINE (
name VARCHAR(30), /* Airline name */
street VARCHAR(30) NOT NULL, /* Airline headquater location - street */
city VARCHAR(30) NOT NULL, /* Airline headquater location - city */
state VARCHAR(20) NOT NULL, /* Airline headquater location - state */
postcode CHAR(10) NOT NULL, /* Airline headquater location - postcode */
country VARCHAR(30) NOT NULL, /* Airline headquater location - country */
phone VARCHAR(20), /* phone number */
website VARCHAR(60), /* Manager start date */
ceo VARCHAR(30), /* CEO of the airline */
CONSTRAINT AIRLINE_PK PRIMARY KEY(name),
CONSTRAINT AIRLINE_CK1 UNIQUE(phone),
CONSTRAINT AIRLINE_CK2 UNIQUE(website));
CREATE TABLE FLIGHT (
name VARCHAR(30), /* airline name */
flightNumber CHAR(6), /* Flight number */
departAirport VARCHAR(50), /* Departure airport */
departTime DATETIME NOT NULL, /* Departure date and time */
arriveAirport VARCHAR(50) NOT NULL, /* Arriving airport */
arriveTime DATETIME, /* Arriving date and time */
capacity DECIMAL(3) NOT NULL, /* Capacity for passengers */
airliner VARCHAR(20) NOT NULL, /* Aircraft for passengers */
CONSTRAINT FLIGHT_PK PRIMARY KEY(flightNumber, departAirport, departTime),
CONSTRAINT FLIGHT_CK1 UNIQUE (flightNumber, arriveAirport, arriveTime),
CONSTRAINT FLIGHT_FK1 FOREIGN KEY (name) REFERENCES AIRLINE(name));
以下是到db Fiddle的链接:https://www.db-fiddle.com/f/jbcqw2udm2ggzdpnmbya6k/0
我收到的结果是:
name: street: city: state: country: maxTotal:
'Virgin Australia', 'B-12 Jianguomenwai Avenue', 'Mascot', 'VIC', 'China', '12'
我需要接收以下输出:
name: street: city: state: country: maxTotal:
'Qantas', '10 Bourke Road', 'Mascot', 'NSW', 'Australia', '12'
如有任何帮助,我们将不胜感激!
我认为这只是一个行限制查询:
select
a.*,
(select count(*) from flight f where f.name = a.name) no_flights
from arline a
order by no_flights desc
limit 1
另一方面,如果您希望允许顶部连接,则可以使用rank()
(在MySQL 8.0中可用):
select *
from
select t.*, rank() over(order by no_flights desc) rn
from (
select
a.*,
(select count(*) from flight f where f.name = a.name) no_flights
from arline a
order by no_flights desc
) t
) t
where rn = 1