我想加入两个表，但表 1 上的每条记录只能得到表 2 的 1 条记录

例如:

SELECT c.id, c.title, p.id AS product_id, p.title
FROM categories AS c
JOIN products AS p ON c.id = p.category_id

这会让我得到 products 中的所有记录，这不是我想要的。我想要每个类别有 1 个 [第一个] 产品(我在产品字段中有一个 sort 列)。

我该怎么做？

最佳答案

我更喜欢类似问题中描述的另一种方法:https://stackoverflow.com/a/11885521/2215679

这种方法更好，尤其是在您需要在 SELECT 中显示多个字段的情况下。为避免 错误代码:1241。操作数应包含 1 列或每列的双重子选择。

对于您的情况，查询应该是这样的(这个查询也可以在 PostgresQL 中工作，而且速度非常快，请参阅下面的更新):

SELECT
 c.id,
 c.title,
 p.id AS product_id,
 p.title AS product_title
FROM categories AS c
JOIN products AS p ON
 p.id = (                                 --- the PRIMARY KEY
  SELECT p1.id FROM products AS p1
  WHERE c.id=p1.category_id
  ORDER BY p1.id LIMIT 1
 )

PS。我对这里提出的查询与其他查询进行了性能测试，这个查询是最好的选择!

更新(2022-07-20，PostgresSQL)

我已经有一段时间没有使用 mySQL，所以，我决定使用 @Gravy 在 PostgresQL v.12.9 中提供的解决方案来测试我的解决方案的性能(它实际上在 MySQL 和 PostgresQL 中都非常完美)。

为此，我决定创建一个包含 100 个类别 和 100000 个产品 的虚拟表和数据。您可以在 this gist 上查看代码

我在上面运行了我的查询，只用了 13ms 即可运行。

在我稍微修改(对于 postgres)来自@Gravy 的查询之后:

SELECT
  id,
  category_title,
  (array_agg(product_title))[1]  
FROM
    (SELECT c.id, c.title AS category_title, p.id AS product_id, p.title AS product_title
    FROM categories AS c
    JOIN products AS p ON c.id = p.category_id
    ORDER BY c.id ASC) AS a 
GROUP BY id, category_title;

并运行它。 在我的机器上花费了超过 150 毫秒。这>慢了 10 倍。

为捍卫@gravy 的解决方案，我同意n+1 问题。但是，在这种特殊情况下，通常产品的数量远大于类别。因此，与@Gravy 查询中的每个产品相比，遍历每个类别的成本要低得多。

顺便说一下，如果你的表有 100 种产品，100 个类别，我的查询速度还是一样的(9-17ms 之间)，但是来自 [@Gravy] 的查询需要超过 2 秒 运行

在简历中，此时此刻，我的查询是当前任务最高效和最优的解决方案。

欢迎评论。

关于MySQL JOIN with LIMIT 1 on join table，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/6879391/