mysql - 在 :many pair of tables in MySQL 中选择一行的最有效方法

Question

假设我在一对多表 city 和 person 中分别获得了以下数据:

SELECT city.*, person.* FROM city, person WHERE city.city_id = person.person_city_id;
+---------+-------------+-----------+-------------+----------------+
| city_id | city_name   | person_id | person_name | person_city_id |
+---------+-------------+-----------+-------------+----------------+
|       1 | chicago     |         1 | charles     |              1 |
|       1 | chicago     |         2 | celia       |              1 |
|       1 | chicago     |         3 | curtis      |              1 |
|       1 | chicago     |         4 | chauncey    |              1 |
|       2 | new york    |         5 | nathan      |              2 |
|       3 | los angeles |         6 | luke        |              3 |
|       3 | los angeles |         7 | louise      |              3 |
|       3 | los angeles |         8 | lucy        |              3 |
|       3 | los angeles |         9 | larry       |              3 |
+---------+-------------+-----------+-------------+----------------+
9 rows in set (0.00 sec)

我想使用一些特定的逻辑为每个独特的城市从 person 中选择一条记录。例如:

SELECT city.*, person.* FROM city, person WHERE city.city_id = person.person_city_id
GROUP BY city_id ORDER BY person_name DESC
;

这里的含义是，在每个城市内，我想得到字典序上最大的值，例如:

+---------+-------------+-----------+-------------+----------------+
| city_id | city_name   | person_id | person_name | person_city_id |
+---------+-------------+-----------+-------------+----------------+
|       2 | new york    |         5 | nathan      |              2 |
|       3 | los angeles |         6 | luke        |              3 |
|       1 | chicago     |         1 | curtis      |              1 |
+---------+-------------+-----------+-------------+----------------+

然而，我得到的实际输出是:

+---------+-------------+-----------+-------------+----------------+
| city_id | city_name   | person_id | person_name | person_city_id |
+---------+-------------+-----------+-------------+----------------+
|       2 | new york    |         5 | nathan      |              2 |
|       3 | los angeles |         6 | luke        |              3 |
|       1 | chicago     |         1 | charles     |              1 |
+---------+-------------+-----------+-------------+----------------+

据我所知，造成这种差异的原因是 MySQL 首先执行 GROUP BY，然后执行 ORDER BY。这对我来说很不幸，因为我希望 GROUP BY 具有选择记录的选择逻辑。

我可以通过使用一些嵌套的 SELECT 语句来解决这个问题:

SELECT c.*, p.* FROM city c,
    ( SELECT p_inner.* FROM
        ( SELECT * FROM person ORDER BY person_city_id, person_name DESC ) p_inner
        GROUP BY person_city_id ) p
    WHERE c.city_id = p.person_city_id;
+---------+-------------+-----------+-------------+----------------+
| city_id | city_name   | person_id | person_name | person_city_id |
+---------+-------------+-----------+-------------+----------------+
|       1 | chicago     |         3 | curtis      |              1 |
|       2 | new york    |         5 | nathan      |              2 |
|       3 | los angeles |         6 | luke        |              3 |
+---------+-------------+-----------+-------------+----------------+

当 person 表增长到任意大时，这似乎是非常低效的。我假设内部 SELECT 语句不知道最外层的 WHERE 过滤器。这是真的吗？

在 GROUP BY 之前有效执行 ORDER BY 的公认最佳方法是什么？

Answer 1

(在 MySQL 中)执行此操作的通常方法是将表与其自身连接。

首先获取每个 city 中最大的 person_name(即 person 表中的每个 person_city_id):

SELECT p.*
FROM person p
LEFT JOIN person p2
 ON p.person_city_id = p2.person_city_id
 AND p.person_name < p2.person_name
WHERE p2.person_name IS NULL

这会在每个 person_city_id(您的 GROUP BY 变量)中将 person 连接到自身，并且还会将表格配对，这样 p2 的person_name 大于p 的person_name。

如果有 p.person_name 而 no greater p2.person_name (在同一城市内)，则它是左连接，则 p2.person_name 将为 NULL。这些正是每个城市“最伟大的”person_name。

因此，要将您的其他信息(来自 city)加入其中，只需执行另一个加入:

SELECT c.*,p.*
FROM person p
LEFT JOIN person p2
 ON p.person_city_id = p2.person_city_id
 AND p.person_name < p2.person_name
LEFT JOIN city c                           -- add in city table
 ON p.person_city_id = c.city_id           -- add in city table
WHERE p2.person_name IS NULL               -- ORDER BY c.city_id if you like