在sql中使用子查询

zzwlnbp8 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(341)

我有4张table：

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))

* /

由于SQLFIDLE长度的限制，此数据被裁剪：

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')

;

mysql subquery

来源：https://stackoverflow.com/questions/48051172/using-sub-query-in-sql

1条答案

按热度按时间

c7rzv4ha1#

如果您想要所有的指南，但首先是最多可拆卸的指南，您可以使用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

如果你需要max向导和其他名字，那么你可以把max的结果和所有向导联系起来

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

赞(0）回复(0）举报 2021-06-21

我来回答

在sql中使用子查询

1条答案

相关问题

热门标签

最新问答