MySQL：选择N行，但在一列中仅包含唯一值

小编典典

MySQL：选择N行，但在一列中仅包含唯一值

sql

给定此数据集：

ID  Name            City            Birthyear
1   Egon Spengler   New York        1957
2   Mac Taylor      New York        1955
3   Sarah Connor    Los Angeles     1959
4   Jean-Luc Picard La Barre        2305
5   Ellen Ripley    Nostromo        2092
6   James T. Kirk   Riverside       2233
7   Henry Jones     Chicago         1899

我需要找到3个最老的人，但是每个城市只有一个。

如果它只是最老的三个，那将是…

亨利·琼斯/芝加哥
麦克·泰勒（Mac Taylor）/纽约
埃贡·斯宾格勒（Egon Spengler）/纽约

但是，由于Egon Spengler和Mac Taylor都位于纽约，因此Egon Spengler会退学，而下一个（Sarah Connor
/洛杉矶）会进来。

有什么优雅的解决方案吗？

更新：

当前，PConroy的一种变体是最好/最快的解决方案：

SELECT P.*, COUNT(*) AS ct
   FROM people P
   JOIN (SELECT MIN(Birthyear) AS Birthyear
              FROM people 
              GROUP by City) P2 ON P2.Birthyear = P.Birthyear
   GROUP BY P.City
   ORDER BY P.Birthyear ASC 
   LIMIT 10;

对于大数据集（5分钟后中止），他最初的“ IN”查询速度非常慢，但是将子查询移至JOIN可以大大提高速度。大约花费了0.15秒。在我的测试环境中为1
mio行。我有一个关于“城市，出生年”的索引，另一个是关于“出生年”的索引。

注意：这与…有关

阅读 229

2021-03-17

共1个答案

小编典典

可能不是最优雅的解决方案，并且IN在更大的桌子上，其性能可能会受到影响。

嵌套查询获取Birthyear每个城市的最小值。只有具有此Birthyear条件的记录才在外部查询中匹配。按年龄排序，然后限制为3个结果，则可以使您成为所在城市中年龄最大的3个最老的人（Egon
Spengler退学。）

SELECT Name, City, Birthyear, COUNT(*) AS ct
FROM table
WHERE Birthyear IN (SELECT MIN(Birthyear)
               FROM table
               GROUP by City)
GROUP BY City
ORDER BY Birthyear DESC LIMIT 3;

+-----------------+-------------+------+----+
| name            | city        | year | ct |
+-----------------+-------------+------+----+
| Henry Jones     | Chicago     | 1899 | 1  |
| Mac Taylor      | New York    | 1955 | 1  |
| Sarah Connor    | Los Angeles | 1959 | 1  |
+-----------------+-------------+------+----+

编辑 -添加GROUP BY City到外部查询中，因为具有相同出生年份的人将返回多个值。如果对外部查询进行分组，则可以确保每个城市仅返回一个结果，如果超过一个人的最小值为Birthyear。该ct列将显示该城市中是否存在不止一个人Birthyear

2021-03-17