MySQL查询需要大量（复杂？）过滤

I have the following database table (People):

我有以下数据库表（人员）：

+--------+--------+--------+
|  Name  |   Age  |  Time  |
+--------+--------+--------+
| Tim    |   30   |  10:10 |
| Jill   |   31   |  10:20 |
| Peter  |   31   |  10:30 |
| Peter  |   33   |  10:40 |
| Jack   |   32   |  10:50 |
| Susan  |   35   |  10:60 |
| Susan  |   35   |  11:70 |
+--------+--------+--------+

Now I want to get 5 names which are the oldest from this list:

现在我想从这个列表中得到5个最老的名字：

Select * FROM People ORDER BY Age DESC LIMIT 5

This will give:

这将给出：

| Susan  |   35   |  11:70 |
| Susan  |   35   |  10:60 |
| Peter  |   33   |  10:40 |
| Jack   |   32   |  10:50 |
| Peter  |   31   |  10:30 |

Now this is not exactly what I want. We see Susan in there twice, same as Peter. I don't want duplicate names to show up. Now we see that the 2 Susans have both the same age, so the Susan with the highest time (mm:ss) should be filter out (in this case: | Susan | 35 | 11:70 |)

现在这不是我想要的。我们看到苏珊在那里两次，和彼得一样。我不希望出现重复的名字。现在我们看到2个Susans都有相同的年龄，所以具有最高时间（mm：ss）的Susan应该被过滤掉（在这种情况下：| Susan | 35 | 11:70 |）

Fot the 2 Peters we see that one Peter is older, so in this case the younger Peter should be filtered out (| Peter | 31 | 10:30 |)

在彼得斯彼得斯看到一个彼得年纪大了，所以在这种情况下，年轻的彼得应该被过滤掉（| Peter | 31 | 10:30 |）

So how would this Query look like? It should first select all, then order them by Age (oldest on top), then filter out the duplicate names where it must first look at the age (lowest gets filtered) and then at the time (highest time gets filter out) and then DESC LIMIT 5.

那么这个查询怎么样？它应首先选择all，然后按Age（最旧的顶部）排序，然后过滤掉必须首先查看年龄的重复名称（最低值被过滤），然后是当时（最高时间过滤掉）然后DESC限制5。

So the only correct result will look like this:

所以唯一正确的结果将是这样的：

| Susan  |   35   |  10:60 |
| Peter  |   33   |  10:40 |
| Jack   |   32   |  10:50 |
| Jill   |   31   |  10:20 |
| Peter  |   31   |  10:30 |

3 个解决方案

#1

SELECT
    Name, Age, MIN(Time) As LowestTime
FROM
    People
GROUP BY 
    Name, Age
ORDER BY
    Age DESC, Name ASC
LIMIT 5

#2

SELECT Name,Age,Max(Time) as Time
FROM People
GROUP BY concat(Age,':',Name)
ORDER BY Age DESC,Time ASC 
LIMIT 5

The GROUP BY takes care of Susan, the ORDER BY of Peter

GROUP BY负责Peter的ORDER BY Susan

#3

I believe this query is what you're looking for. It filters by Name:Age, then Name:Time, and finally does a final group by Name and ORDER as necessary.

我相信这个查询就是你要找的。它按名称过滤：年龄，然后名称：时间，最后根据需要按名称和订单执行最终组。

Query

询问

SELECT `Name`, `Age`, `Time`
FROM `People`
WHERE concat(`Name`,'-',`Time`)
 IN (
  SELECT concat(`Name`,'-',MAX(`Time`))
  FROM `People`
  WHERE concat(`Name`,'-',`Age`)
  IN (
   SELECT concat(`Name`,'-',MIN(`Age`))
   FROM `People`
   GROUP BY `Name`
  )
  GROUP BY `Name`
 )
 GROUP BY `Name`
 ORDER BY `Age` DESC
 LIMIT 5

Note: While this is a valid query and returns the results you're looking for, it can get pretty expensive, especially with a larger table. If you plan to do this filtering on a larger table, I would suggest adding a column where you concatenate these fields manually, and set up an index against both columns. Also, I'm assuming this was just a quick example, but if you should also have an id column set up that is PRIMARY and auto_increment.

注意：虽然这是一个有效的查询并返回您正在寻找的结果，但它可能会非常昂贵，尤其是对于更大的表。如果您计划在较大的表上进行此筛选，我建议您添加一个列，您可以手动连接这些字段，并为这两列设置索引。另外，我假设这只是一个简单的例子，但如果你还应该设置一个id列，那就是PRIMARY和auto_increment。

#1

SELECT
    Name, Age, MIN(Time) As LowestTime
FROM
    People
GROUP BY 
    Name, Age
ORDER BY
    Age DESC, Name ASC
LIMIT 5

#2

SELECT Name,Age,Max(Time) as Time
FROM People
GROUP BY concat(Age,':',Name)
ORDER BY Age DESC,Time ASC 
LIMIT 5