从列中选择MAX值，并从另一列中选择相应的值 [英] Select MAX value from column and corresponding value from another

问题描述

这可能是一个相对简单的问题，但我正在努力解决.我下面列出了三个表(所有者，宠物，petTypes )，并尝试在一个查询中提取以下所有数据.前两个并不难，但是第三和第四是我挣扎的地方.

如果要复制表数据:

解决方案

正在运行的演示:

 选择O.ID，O.FirstName，O.LastName，P.Age，min(PT.Name)作为TypeName，OPets.CntOfOtherPets来自业主OINNER JOIN(SELECT OwnerID，max(Age)MA，count(*)-1 as CntOfOtherPets来自宠物GROUP BY OwnerID)选项在OPets.OwnerID = O.ID上内连宠物P在P.Age = OPets.MA和P.OwnerID = OPets.OwnerID内部联接PetTypes PT在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: other 的数量 宠物.即使在年龄关系上，这个#也将是正确的.因为count-1永远是其他宠物"

This might be a relatively simple question but I am struggling to work it out. I have three tables all listed below (owners, pets, petTypes) and trying to extract the following data all in one query. The first two are not difficult but the 3rd and 4th is where I am struggling.

Table data if you want to replicate: https://pastebin.com/veXHwcMc

The questions:

1. Owner Id

2. Owner name

3. Owners oldest pet age
4. Owners oldest pet type name
5. Number of other pets

What I have tried

Selecting the oldest age SELECT MAX(age) FROM pets

Joining the tables to show both SELECT pets.ownerId, MAX(pets.age), petTypes.name FROM pets INNER JOIN petTypes ON pets.petTypeId = petTypes.id GROUP BY pets.ownerId;

But this is wrong. Because they are all showing cat when they should be showing the correct name for the oldest cat.

I cam upon this question: How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

So I attempted: SELECT petTypes.id, petTypes.name FROM petTypes INNER JOIN (SELECT MAX(age) FROM pets GROUP BY ownerId) pets ON petTypes.id = pets.petTypeId;

But the error thrown is ERROR 1054 (42S22): Unknown column 'petTypes.id' in 'on clause'

Any help please

解决方案

Working demo:

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 ;

The derived table OPets gets a count of all the pets by owner less 1 since we know this represents the "other pet" count. The max pet age is returned via aggregation grouped owner. By joining this data set back to the pet set we get only the pets for that owner having the max age. We then join to petType to get the name of that type of pet (or pets if multiple share same max age).

Finally, group by all the fields except the PT.Name, and select the min(PetType.Name). That way if multiple animals exist with the same oldest age, we pick the one with TypeName with the earliest name alphabetically. This approach only works given the data being returned. If a petname, or the petID had to be returned, a different approach would be needed; but this was by far the simplest approach given the desired results (columns returned)

The count-1 seems a bit hackish since count(*) returns a count of all pets -1 simply subtracts out the 1 we know we had for the #5 requirement: Number of other pets. Even in the case of ties on age this # will be correct. Since count-1 will always be the "other pets"

这篇关于从列中选择MAX值，并从另一列中选择相应的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！