mysql - 在sql中使用子查询

Question

我有 4 个表:

Travelers (TravelerID,FirstName,LastName) 
Guides(GuideID,FirstName,LastName) 
Locations(LocationID,LocationName) 
Trips(TravelerID,GuideID,LocationID,Stars,StartDate,ReturnDate)

我想为每个导游返回他引导最多旅客的地点名称。结果需要包含所有导游，即使他们根本没有旅客

我尝试使用此子查询，但它不起作用:

SELECT G.FirstName,L.LocationName,count(distinct(TravelerID))as 
number_of_travelers_per_guide 
FROM Guides AS G 
LEFT JOIN Trips AS T USING (GuideID) 
LEFT JOIN Locations AS L USING (LocationID)
GROUP BY G.FirstName,L.LocationName 
HAVING max((SELECT T1.number_of_travelers_per_guide 
        FROM Trips AS T1 
        WHERE T.GuideID=T1.GuideID));

结果应该是这样的:

我将不胜感激任何帮助

<小时/>

由我编辑Patrick Artner :

/* data creation script - python 3.6.2

import random

random.seed(815) # fixed 0815 seed

Travelers = [[x,'a' + str(x), 'A' + str(x)] for x in range(20)] 

Guides = [ [x,"guide_" + str(x), "G_" + str(x)] for x in range(10)]

Locations = [[x, "location_" + str(x)] for x in range(6)] 

# Trips(TravelerID,GuideID,LocationID,Stars,StartDate,ReturnDate)
Trips = []
for n in range(300):
    Trips.append([random.choice(range(20)),random.choice(range(10)),random.choice(range(6)),random.randint(1,6),None, None])


def prnList(lst):
    def prn(lst):
        for i in lst:
            yield " ,".join([str(x) for x in i]) + "\n"
        yield "\n\n" 
    return "".join(prn(lst))

with open("demodata.txt","w") as f:
    f.write("Travelers\nTravelerID,FirstName,LastName\n")
    f.write(prnList(Travelers))
    f.write("Guides\nGuideID,FirstName,LastName\n") 
    f.write(prnList(Guides))
    f.write("Locations\nLocationID,LocationName\n") 
    f.write(prnList(Locations)) 
    f.write("Trips\nTravelerID,GuideID,LocationID,Stars,StartDate,ReturnDate\n")
    f.write(prnList(Trips))
*/

由于 sqlfiddle 长度的限制，此数据被裁剪:

CREATE TABLE   Travelers     (`TravelerID` int, `FirstName` varchar(3), `LastName` varchar(3));

INSERT INTO   Travelers    (`TravelerID`, `FirstName`, `LastName`)
VALUES
    (0, 'a0', 'A0'),
    (1, 'a1', 'A1'),
    (2, 'a2', 'A2'),
    (3, 'a3', 'A3'),
    (4, 'a4', 'A4'),
    (5, 'a5', 'A5'),
    (6, 'a6', 'A6'),
    (7, 'a7', 'A7'),
    (8, 'a8', 'A8'),
    (9, 'a9', 'A9'),
    (10, 'a10', 'A10'),
    (11, 'a11', 'A11'),
    (12, 'a12', 'A12'),
    (13, 'a13', 'A13'),
    (14, 'a14', 'A14'),
    (15, 'a15', 'A15'),
    (16, 'a16', 'A16'),
    (17, 'a17', 'A17'),
    (18, 'a18', 'A18'),
    (19, 'a19', 'A19')
;


CREATE TABLE Guides     (`GuideID` int, `FirstName` varchar(7), `LastName` varchar(3));

INSERT INTO Guides     (`GuideID`, `FirstName`, `LastName`)
VALUES
    (0, 'guide_0', 'G_0'),
    (1, 'guide_1', 'G_1'),
    (2, 'guide_2', 'G_2'),
    (3, 'guide_3', 'G_3'),
    (4, 'guide_4', 'G_4'),
    (5, 'guide_5', 'G_5'),
    (6, 'guide_6', 'G_6'),
    (7, 'guide_7', 'G_7'),
    (8, 'guide_8', 'G_8'),
    (9, 'guide_9', 'G_9')
;


CREATE TABLE Locations    (`LocationID` int, `LocationName` varchar(10));

INSERT INTO Locations    (`LocationID`, `LocationName`)
VALUES
    (0, 'location_0'),
    (1, 'location_1'),
    (2, 'location_2'),
    (3, 'location_3'),
    (4, 'location_4'),
    (5, 'location_5')
;


CREATE TABLE Trips    (`TravelerID` int, `GuideID` int, `LocationID` int, `Stars` int, `StartDate` varchar(4), `ReturnDate` varchar(4));

INSERT INTO Trips    (`TravelerID`, `GuideID`, `LocationID`, `Stars`, `StartDate`, `ReturnDate`)
VALUES
    (0, 4, 0, 5, 'None', 'None'),
    (9, 5, 3, 1, 'None', 'None'),
    (16, 9, 0, 6, 'None', 'None'),
    (6, 3, 2, 4, 'None', 'None'),
    (5, 0, 2, 4, 'None', 'None'),
    (1, 4, 4, 5, 'None', 'None'),
    (5, 4, 1, 1, 'None', 'None'),
    (8, 7, 3, 3, 'None', 'None'),
    (8, 2, 1, 5, 'None', 'None'),
    (5, 6, 3, 5, 'None', 'None'),
    (2, 8, 1, 3, 'None', 'None'),
    (17, 0, 1, 4, 'None', 'None'),
    (6, 8, 3, 4, 'None', 'None'),
    (0, 3, 3, 2, 'None', 'None'),
    (4, 8, 3, 4, 'None', 'None'),
    (16, 5, 0, 6, 'None', 'None'),
    (10, 9, 5, 4, 'None', 'None'),
    (19, 8, 0, 4, 'None', 'None'),
    (7, 7, 4, 3, 'None', 'None'),
    (7, 4, 1, 5, 'None', 'None'),
    (13, 7, 5, 6, 'None', 'None'),
    (0, 9, 5, 2, 'None', 'None'),
    (4, 5, 5, 5, 'None', 'None'),
    (19, 1, 2, 3, 'None', 'None'),
    (2, 6, 1, 1, 'None', 'None'),
    (13, 3, 0, 4, 'None', 'None'),
    (8, 0, 0, 2, 'None', 'None'),
    (18, 6, 2, 5, 'None', 'None'),
    (14, 4, 5, 3, 'None', 'None'),
    (12, 8, 1, 6, 'None', 'None'),
    (8, 1, 3, 4, 'None', 'None'),
    (15, 1, 5, 2, 'None', 'None'),
    (5, 1, 4, 6, 'None', 'None'),
    (5, 5, 1, 1, 'None', 'None'),
    (14, 6, 0, 5, 'None', 'None'),
    (3, 8, 3, 6, 'None', 'None'),
    (3, 1, 1, 4, 'None', 'None'),
    (17, 1, 0, 4, 'None', 'None'),
    (6, 4, 0, 1, 'None', 'None'),
    (16, 6, 5, 6, 'None', 'None'),
    (6, 8, 3, 1, 'None', 'None'),
    (13, 8, 1, 1, 'None', 'None'),
    (13, 3, 5, 5, 'None', 'None'),
    (12, 4, 4, 2, 'None', 'None'),
    (13, 8, 3, 3, 'None', 'None'),
    (3, 1, 1, 1, 'None', 'None'),
    (6, 4, 2, 2, 'None', 'None'),
    (0, 8, 0, 6, 'None', 'None'),
    (3, 8, 3, 2, 'None', 'None'),
    (17, 7, 1, 3, 'None', 'None'),
    (0, 4, 4, 1, 'None', 'None'),
    (13, 0, 2, 6, 'None', 'None'),
    (5, 5, 2, 6, 'None', 'None'),
    (14, 8, 0, 2, 'None', 'None'),
    (12, 0, 1, 2, 'None', 'None'),
    (3, 7, 3, 1, 'None', 'None'),
    (8, 2, 0, 2, 'None', 'None'),
    (17, 0, 4, 4, 'None', 'None'),
    (4, 6, 0, 4, 'None', 'None'),
    (2, 2, 1, 1, 'None', 'None'),
    (9, 6, 4, 6, 'None', 'None'),
    (11, 1, 4, 3, 'None', 'None'),
    (6, 5, 2, 3, 'None', 'None'),
    (1, 5, 5, 5, 'None', 'None'),
    (13, 6, 3, 3, 'None', 'None'),
    (15, 4, 5, 2, 'None', 'None'),
    (5, 3, 5, 2, 'None', 'None'),
    (2, 5, 5, 5, 'None', 'None'),
    (6, 2, 1, 1, 'None', 'None'),
    (19, 0, 1, 4, 'None', 'None'), 
    (15, 3, 3, 1, 'None', 'None'),
    (13, 1, 5, 1, 'None', 'None'),
    (14, 8, 3, 4, 'None', 'None'),
    (19, 5, 4, 5, 'None', 'None'),
    (17, 1, 0, 5, 'None', 'None'),
    (6, 8, 3, 4, 'None', 'None'),
    (0, 4, 4, 2, 'None', 'None'),
    (16, 5, 3, 4, 'None', 'None'),
    (13, 4, 5, 6, 'None', 'None'),
    (0, 8, 4, 3, 'None', 'None'),
    (10, 0, 3, 3, 'None', 'None'),
    (0, 8, 0, 4, 'None', 'None'),
    (9, 6, 5, 1, 'None', 'None'),
    (9, 7, 3, 5, 'None', 'None'),
    (18, 2, 4, 3, 'None', 'None'),
    (15, 9, 3, 5, 'None', 'None'),
    (5, 5, 3, 3, 'None', 'None'),
    (17, 4, 1, 4, 'None', 'None'),
    (7, 6, 4, 5, 'None', 'None'),
    (7, 9, 3, 5, 'None', 'None'),
    (12, 3, 3, 3, 'None', 'None'),
    (13, 2, 3, 6, 'None', 'None'),
    (17, 8, 5, 5, 'None', 'None'),
    (15, 8, 0, 3, 'None', 'None'),
    (3, 3, 2, 4, 'None', 'None'),
    (11, 1, 2, 5, 'None', 'None'),
    (17, 1, 0, 3, 'None', 'None'),
    (17, 4, 0, 5, 'None', 'None'),
    (1, 5, 1, 4, 'None', 'None'),
    (16, 8, 4, 4, 'None', 'None'),
    (15, 5, 3, 3, 'None', 'None'),
    (17, 9, 4, 1, 'None', 'None'),
    (1, 8, 2, 5, 'None', 'None'),
    (15, 9, 2, 6, 'None', 'None'),
    (17, 2, 1, 1, 'None', 'None'),
    (12, 1, 2, 6, 'None', 'None'),
    (6, 9, 1, 3, 'None', 'None'),
    (5, 1, 3, 5, 'None', 'None'),
    (8, 1, 5, 2, 'None', 'None'),
    (12, 9, 1, 5, 'None', 'None'),
    (3, 4, 0, 5, 'None', 'None'),
    (11, 6, 2, 4, 'None', 'None'),
    (8, 0, 5, 1, 'None', 'None'),
    (5, 1, 1, 1, 'None', 'None'),
    (15, 3, 0, 3, 'None', 'None'),
    (15, 1, 2, 4, 'None', 'None'),
    (2, 6, 1, 5, 'None', 'None'),
    (19, 7, 4, 6, 'None', 'None'),
    (2, 2, 4, 1, 'None', 'None'),
    (19, 2, 2, 6, 'None', 'None'),
    (10, 4, 4, 2, 'None', 'None'),
    (0, 1, 1, 1, 'None', 'None'),
    (7, 2, 4, 3, 'None', 'None'),
    (16, 5, 3, 4, 'None', 'None'),
    (11, 3, 4, 3, 'None', 'None'),
    (15, 1, 2, 5, 'None', 'None'),
    (9, 4, 0, 3, 'None', 'None'),
    (16, 3, 5, 5, 'None', 'None'),
    (7, 8, 4, 6, 'None', 'None'),
    (14, 5, 0, 5, 'None', 'None'),
    (19, 6, 3, 1, 'None', 'None'),
    (17, 5, 3, 5, 'None', 'None'),
    (12, 7, 0, 5, 'None', 'None'),
    (7, 0, 1, 2, 'None', 'None'),
    (0, 1, 4, 4, 'None', 'None'),
    (16, 2, 0, 3, 'None', 'None')

;

Answer 1

如果您想要所有指南，但首先需要最大数量的 Teavelers 指南，您可以使用 desc 订单，例如:

  SELECT G.FirstName, L.LocationName, count(distinct(TravelerID))as number_of_travelers_per_guide 
  FROM Guides AS G 
  LEFT JOIN Trips AS T USING (GuideID) 
  LEFT JOIN Locations AS L USING (LocationID)
  GROUP BY G.FirstName,L.LocationName 
  ORDER BY number_of_travelers_per_guide DESC

如果您需要最大指南和其他名称，那么您可以将最大指南的结果与所有指南结合使用

select Guides.FirstName, my_t2.LocationName,  my_t2.number_of_travelers_per_guide 
from (

    SELECT G.GuideId, G.FirstName,L.LocationName, count(distinct T.TravelerID ) as number_of_travelers_per_guide 
    FROM Guides AS G 
    LEFT JOIN Trips AS T USING (GuideID) 
    LEFT JOIN Locations AS L USING (LocationID)
    GROUP BY G.FirstName,L.LocationName 
    HAVING count(distinct TravelerID ) = ( 
                select max(my_count) from (SELECT count(distinct T.TravelerID) my_count
                FROM Guides AS G 
                LEFT JOIN Trips AS T USING (GuideID) 
                LEFT JOIN Locations AS L USING (LocationID)
                GROUP BY G.FirstName, L.LocationName ) my_t )
  )  my_t2
left join Guides on Guides.GuideID = my_t2.GuideID