mysql - 选择最大计数，其中一列决定分组类型

Question

帮助

在只使用一家公司的客户中，找到飞行次数最多的不同乘客。结果集:乘客姓名、乘车次数

[详情] http://i.stack.imgur.com/cBr7E.jpg [检查这个]

我有这个查询

SELECT P.name, COUNT(T.trip_no) AS journeys, C.name as namecompany
FROM
Passenger P, Trip T, Company C, Pass_in_trip PIT
WHERE
P.ID_psg = PIT.ID_psg AND PIT.trip_no = T.trip_no
AND T.ID_comp = C.ID_comp
AND
NOT EXISTS (SELECT * FROM
Passenger, Trip, Company, Pass_in_trip
WHERE Pass_in_trip.ID_psg = P.ID_psg AND Pass_in_trip.trip_no = Trip.trip_no
AND Trip.ID_comp <> C.ID_comp)
GROUP BY P.name, C.name;

我有这个结果集:

# name, journeys, namecompany
'Alan Rickman', '1', 'Don_avia'
'George Clooney', '1', 'Dale_avia'
'Harrison Ford', '3', 'British_AW'
'Michael Caine', '4', 'British_AW'
'Mullah Omar', '4', 'British_AW'
'Nikole Kidman', '3', 'Don_avia'
'Russell Crowe', '1', 'British_AW'
'Steve Martin', '2', 'British_AW'

我想通过一对一点赞来分离结果

# name, journeys, namecompany
'Michael Caine', '4', 'British_AW'
'Nikole Kidman', '3', 'Don_avia'
'George Clooney', '1', 'Dale_avia'

Answer 1

在此查询中，您可以按公司名称获取最大值。试试这个

SELECT subquery.name, MAX(subquery.journeys),subquery.namecompany FROM
(
    SELECT P.name, COUNT(T.trip_no) AS journeys, C.name as namecompany
    FROM Passenger P, Trip T, Company C, Pass_in_trip PIT
    WHERE P.ID_psg = PIT.ID_psg AND PIT.trip_no = T.trip_no
    AND T.ID_comp = C.ID_comp
    AND NOT EXISTS (SELECT * FROM
       Passenger, Trip, Company, Pass_in_trip
       WHERE Pass_in_trip.ID_psg = P.ID_psg 
       AND Pass_in_trip.trip_no =    Trip.trip_no
       AND Trip.ID_comp <> C.ID_comp)
       GROUP BY P.name, C.name
)AS subquery GROUP BY subquery.namecompany;