目前我有它可以找到一個城市中最古老的店員，但不知何故我必須將它更改為一個城市商店中最年長的店員。我曾嘗試在其中添加 StoreID，但它只會把事情搞砸。我還添加了關系的圖片
SELECT Name AS [Name of the oldest clerk]
FROM Clerks AS c, Cities AS ct, Stores AS s
WHERE ct.CityID=c.CityID
AND s.StoreID=c.StoreID
AND City=[Name of the city]
AND Age=(SELECT MAX(Age)
FROM Clerks AS c, Cities AS ct
WHERE ct.CityID=c.City
AND City = [Name of the city]);
uj5u.com熱心網友回復：
尚未測驗，只是一個想法。我一會兒再仔細看看。
select cy.city,c.name,c.age,max_age.mx_age
from
clerks as c
join stores as s on c.storeid=s.storeid
join cities as cy on s.cityid=cy.cityid
join
(
select s2.cityid,max(c2.age) as mx_age from stores as s2
left join clerks as c2 on s2.storeid=c2.storeid
group by s2.cityid
) as max_age_by_city on s.cityid=max_age_by_city.cityid and c.age=max_age_by_city.mx_age
order by cy.city,c.age,c.name
uj5u.com熱心網友回復：
Select c.*
from Clerks c
join Stores s on c.StoreID = s.StoreID
join Cities ct on ct.CityID= s.CityID
join ( select max(C2.Age) as Age, S2.CityID
from Clerks as C2
join Stores as S2 on C2.StoreID = S2.StoreID
group by S2.CityID) as MaxAge
on c.Age = MaxAge.Age
and s.CityID = MaxAge.CityID
where ct.City="London"
我已將 (max(Clerks.Age), Stores.City) 的元組加入 Clerks
在這里提琴
編輯：加入 Strores 表以按商店所在城市查找最大年齡
uj5u.com熱心網友回復：
沒有所有這些連接：
SELECT ... FROM CLERKS C
WHERE NOT EXISTS (SELECT 1 FROM CLERKS C2
WHERE C2.CITYID = C.CITYID AND C2.AGE > C.AGE)
SELECT ... FROM CLERKS C
WHERE NOT EXISTS (SELECT 1 FROM CLERKS C2
WHERE C2.STOREID = C.STOREID AND C2.AGE > C.AGE)
至于“城市商店里最年長的店員”，它應該成為
SELECT ... FROM CLERKS C JOIN STORES S ON ...
WHERE NOT EXISTS (SELECT 1 FROM CLERKS C2
WHERE C2.STOREID = C.STOREID AND C2.AGE > C.AGE)
AND S.CITYID = ...
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/478046.html