这可能是一个相对简单的问题,但我正在努力解决。我有下面列出的所有三个表(owners, pets, petTypes
),并试图在一个查询中全部提取以下数据。前两个并不难,但是第三和第四是我努力的地方。
如果要复制表数据:https://pastebin.com/veXHwcMc
问题:
拥有者编号
所有者名称
拥有者年龄最大的宠物
主人最老的宠物类型名称
其他宠物数
我尝试过的
选择最老的年龄SELECT MAX(age) FROM pets
连接表以显示两个SELECT pets.ownerId, MAX(pets.age), petTypes.name FROM pets INNER JOIN petTypes ON pets.petTypeId = petTypes.id GROUP BY pets.ownerId;
但这是错误的。因为他们都在显示猫,所以应该为最老的猫显示正确的名称。
我解决了这个问题:How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?
所以我尝试:SELECT petTypes.id, petTypes.name FROM petTypes INNER JOIN (SELECT MAX(age) FROM pets GROUP BY ownerId) pets ON petTypes.id = pets.petTypeId;
但是抛出的错误是ERROR 1054 (42S22): Unknown column 'petTypes.id' in 'on clause'
任何帮助请
最佳答案
SELECT O.ID, O.FirstName, O.LastName, P.Age, min(PT.Name) as TypeName, OPets.CntOfOtherPets
FROM Owners O
INNER JOIN (SELECT OwnerID, max(Age) MA, count(*)-1 as CntOfOtherPets
FROM Pets
GROUP BY OwnerID) OPets
on OPets.OwnerID = O.ID
INNER JOIN Pets P
on P.Age = OPets.MA
and P.OwnerID = OPets.OwnerID
INNER JOIN PetTypes PT
on P.PetTypeID = PT.ID
GROUP BY O.ID, O.FirstName, O.LastName, P.Age, OPets.CntOfOtherPets ;
派生表OPets通过所有者减去1得到所有宠物的计数,因为我们知道这表示“其他宠物”计数。宠物的最大年龄可以通过聚合分组所有者返回。通过将此数据集重新加入宠物集,我们只能获得拥有最大年龄的那个主人的宠物。然后,我们加入petType以获取该类型宠物的名称(如果多个宠物共享相同的最大年龄,则为宠物)。
最后,对除PT.Name之外的所有字段进行分组,然后选择min(PetType.Name)。这样,如果存在多个具有相同最老年龄的动物,我们将按字母顺序从最早的名字中选择具有TypeName的动物。仅在返回数据的情况下,此方法才有效。如果必须返回宠物名或宠物ID,则需要采用其他方法。但这是迄今为止获得所需结果(返回的列)的最简单方法
count-1似乎有点骇人听闻,因为count(*)返回所有宠物的数量-1只是减去了我们对于#5要求所拥有的1:其他宠物的数量。即使在年龄关系上,这个#也是正确的。由于count-1将永远是“其他宠物”
关于mysql - 从列中选择MAX值,并从另一列中选择相应的值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/47228127/