MySQL 中多对表中 SELECT 一行的最有效方法

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首先执行GR​​OUP 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 在每个 person_city_id（您的 GROUP BY 变量）中与其自身，并且还将表配对，使得 p2 的person_name 是大于 p 的 person_name。

由于如果存在 p.person_name 且没有更大的 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

Answer 2

您的“解决方案”不是有效的 SQL，但它可以在 MySQL 中使用。但是，您无法确定它是否会随着查询优化器代码的未来更改而中断。可以稍微改进为只有 1 层嵌套（仍然不是有效的 SQL）：

--- Option 1 ---
SELECT 
       c.*
     , p.* 
FROM 
      city AS c
  JOIN
      ( SELECT * 
        FROM person 
        ORDER BY person_city_id
               , person_name DESC 
      ) AS p
    ON  c.city_id = p.person_city_id
GROUP BY p.person_city_id

---

另一种方法（有效的 SQL 语法，也适用于其他 DBMS）是创建一个子查询来选择每个城市的姓氏，然后加入：

--- Option 2 ---
SELECT 
       c.*
     , p.* 
FROM 
      city AS c
  JOIN
      ( SELECT person_city_id
             , MAX(person_name) AS person_name 
        FROM person 
        GROUP BY person_city_id
      ) AS pmax
    ON  c.city_id = pmax.person_city_id
  JOIN 
      person AS p
    ON  p.person_city_id = pmax.person_city_id
    AND p.person_name = pmax.person_name

---

另一种方法是自连接（表 person），使用 @mathematical_coffee 描述的 < 技巧。

--- Option 3 ---
  see @mathematical-coffee's answer

---

另一种方法是使用 LIMIT 1 子查询来连接 city 和 person：

--- Option 4 ---
SELECT 
       c.*
     , p.* 
FROM 
      city AS c
  JOIN
      person AS p
    ON
      p.person_id =
      ( SELECT person_id
        FROM person AS pm 
        WHERE pm.person_city_id = c.city_id
        ORDER BY person_name DESC
        LIMIT 1
      ) 

这将运行一个子查询（在表 person）对于每个城市，如果您有 InnoDB 引擎的 (person_city_id, person_name) 索引或 (person_city_id, person_name, person_id)为了MyISAM 引擎。

---

这些选项之间有一个主要区别：

Oprions 2 和 3 将返回所有并列结果（如果您在一个城市中有两个或两个以上的人具有相同的姓名且按字母顺序排列在最后，则将显示两个或全部）。

选项 1 和 4 将为每个城市返回一个结果，即使存在平局。您可以通过更改 ORDER BY 子句来选择哪一个。

---

哪种选项更有效还取决于数据的分布，因此最好的方法是尝试所有选项，检查它们的执行计划并找到最适合每个选项的索引。 (person_city_id, person_name) 上的索引很可能适合任何这些查询。

对于分布，我的意思是：

• 您的城市很少，但每个城市都有很多人吗？ （我认为在这种情况下选项 2 和 4 会表现得更好）

• 或者许多城市每个城市的人口很少？ （对于这样的数据，选项 3 可能更好）。