MySQL返回连接表的第一行

Question

我有两个表（country & ducks），其中country表包含世界上的每个国家，而ducks表具有一个country_id字段，用于链接到主要的country表中，其中包含一个鸭子列表。

我正在尝试获取仅包含至少一个鸭子的国家列表，并在其中为该国家内评分最高的鸭子获取单个匹配记录。到目前为止，我所拥有的是：

SELECT *
FROM country c 
INNER JOIN ducks d ON c.id = d.country_id
ORDER BY c.country ASC, d.rating DESC

这会返回每个国家的所有鸭子列表，而不是每个国家仅返回一个。

如果有人能指点我正确的方向，我将不胜感激。我希望通过SQL来完成这个任务，而不是为每个国家单独编写查询以提取最高评价的鸭子。

- Al_

---

你想要哪只鸭子？随便一只吗？ - dotoree

@dotoree 最高评分（来自该集合的最高鸭子评分） - J. Bruni

Answer 1

SELECT c.*, d.*
FROM country c 
  INNER JOIN ducks d 
    ON d.id =                         --- guessing the ducks Primary Key here
       ( SELECT dd.id                 --- and here  
         FROM ducks dd
         WHERE c.id = dd.country_id
         ORDER BY dd.rating DESC
         LIMIT 1
       )

为 MyISAM 表创建 (country_id, rating, id) 索引，或者为 InnoDB 表创建 (country_id, rating) 索引将会有帮助。

这个查询只会显示每个国家的一个duck，即使有多个鸭子具有相同的评分。如果你想要出现评分相同的鸭子，请使用@imm的GROUP BY答案。

- ypercubeᵀᴹ

---

非常好。完全不使用子查询能实现吗？ - J. Bruni

@J.Bruni：是的，如果你再加一个连接，就像Naltharial的回答一样（实际上是一个带有Where-Is-Null检查的左连接）。 - ypercubeᵀᴹ

Answer 2

你可以尝试添加一个选择连接，针对IT技术相关内容。

SELECT c.*, d.*
FROM country c 
INNER JOIN ducks d ON c.id = d.country_id
LEFT JOIN ducks d2 ON d.country_id = d2.country_id AND d2.rating > d.rating
WHERE d2.id IS NULL

- Naltharial

---

最好使用SELECT c.*, d.*。不需要包括d2.*列 - 这些列将全部为NULL。 - ypercubeᵀᴹ

删除子查询很有趣，但是如果“ducks”表足够大，这个版本看起来非常慢且占用内存。因为它操作的是count(ducks)*count(ducks)集合。 - zxcat

修正：它操作的是 count(ducks)*(count(ducks)-1) 个集合（不是整个 ducks，而是每个 country_id）。好吧，当按 country_id 分开时，这并不那么可怕。但是如果使用适当的索引，@imm 的解决方案应该更有效。 - zxcat

@zxcat：这个查询的复杂性完全不在于此。分组和比较评级的操作要比查看您查询的空间昂贵得多。子查询几乎从来不比JOIN更快，特别是在后者使用eq_ref与子查询的索引进行比较的情况下（理想情况下）。对查询运行EXPLAIN SELECT并比较执行计划。 - Naltharial

Answer 3

你可以尝试以下方法：

SELECT c.*, d.*
FROM country c
INNER JOIN (
    SELECT d.country_id, d.id, MAX(d.rating) AS rating
    FROM ducks d
    GROUP BY d.country_id
) q ON (q.country_id = c.id)

INNER JOIN ducks d 
    ON (d.country_id, d.rating) = (q.country_id, q.rating)

- imm

---

只需要一个连接就可以显示鸭子的详细信息：INNER JOIN ducks d ON (d.country_id, d.rating) = (q.country_id, q.rating)。 - ypercubeᵀᴹ

Answer 4

试试这个：

SELECT c.country, MAX(d.rating) AS max_rating
FROM country c
JOIN ducks d ON c.id = d.country_id
GROUP BY c.id
ORDER BY c.country ASC

如果“最高评分”为1，则将MAX(d.rating)更改为MIN(d.rating)。

- J. Bruni

---

嗨，这很接近了，因为它返回了一个具有最高评分的单个国家，但我也想返回对应于该最高评分的记录。例如，如果我将您代码的第一行更改为SELECT c.country, d.id, d.rating, MAX(d.rating)，那么d.rating并不总是等于MAX(d.rating)，如果这样说的话。 - Al_

@ypercube：谢谢。算了吧……我现在不能在这里测试它。我想让它在没有子查询的情况下实现。现在OP已经解决了他的问题…… - J. Bruni

Answer 5

许多数据库都有类似于“select top 10 * from...”的功能。在mySql中，语法应该是“select * from ... limit 10”。

但是，在这种情况下，你真正想要的是“group by”和“max()”！

- paulsm4

---

嗨，GROUP BY 限制了结果，但 MAX() 函数只显示组的最大值，而行的其余部分包含 MySQL 找到的第一条记录的数据，而不是具有最大评级的记录。这是我目前的查询语句：SELECT c.*, d.*, MAX(d.rating) FROM country c INNER JOIN ducks d ON c.id = d.country_id GROUP BY c.country ORDER BY c.country ASC, d.rating DESC - Al_