提问者:小点点

如何通过查询找到组中的最高计数? -MYSQL


我需要找到航班总数最大的航空公司的名称,街道,城市,州和国家。

下面是我到目前为止的代码:

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'

如有任何帮助,我们将不胜感激!


共1个答案

匿名用户

我认为这只是一个行限制查询:

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