MySQL JOIN 连接表上的 LIMIT 1

MySQL JOIN with LIMIT 1 on joined table

提问by John Davidson

I want to join two tables, but only get 1 record of table2 per record on table1

我想加入两个表，但 table1 上的每条记录只能获得 table2 的 1 条记录

For example:

例如：

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

This would get me all records in products, which is not what I want. I want 1 [the first] product per category (I have a sortcolumn in the products field).

这将使我获得所有记录products，这不是我想要的。我想要每个类别 1 个 [第一个] 产品（我sort在产品字段中有一个列）。

How do I go about doing that?

我该怎么做？

采纳答案by goggin13

I would try something like this:

我会尝试这样的事情：

SELECT C.*,
      (SELECT P.id, P.title 
       FROM products as P
       WHERE P.category_id = C.id
       LIMIT 1)
FROM categories C

回答by Kostanos

I like more another approach described in a similar question: https://stackoverflow.com/a/11885521/2215679

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

This approach is better especially in case if you need to show more than one field in SELECT. To avoid Error Code: 1241. Operand should contain 1 column(s)or double sub-select for each column.

这种方法更好，尤其是当您需要在 SELECT 中显示多个字段时。避免Error Code: 1241. Operand should contain 1 column(s)或对每列进行双重子选择。

For your situation the Query should looks like:

对于您的情况，查询应如下所示：

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
 )

回答by Gravy

Accepted answer by @goggin13 looks wrong. Other solutions provided to-date will work, but suffer from the n+1 problem and as such, suffer a performance hit.

@goggin13 接受的答案看起来是错误的。迄今为止提供的其他解决方案也可以使用，但会遇到 n+1 问题，因此性能会受到影响。

n+1 problem: If there are 100 categories, then we would have to do 1 select to get the categories, then for each of the 100 categories returned, we would need to do a select to get the products in that category. So 101 SELECT queries would be performed.

n+1 问题：如果有 100 个类别，那么我们必须执行 1 次选择来获取类别，然后对于返回的 100 个类别中的每一个，我们都需要执行一次选择以获取该类别中的产品。因此将执行 101 个 SELECT 查询。

My alternative solution solves the n+1 problem and consequently should be significantly more performant as only 2 selects are being performed.

我的替代解决方案解决了 n+1 问题，因此性能应该更高，因为只执行了 2 个选择。

SELECT
  *
FROM
    (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
    ORDER BY c.id ASC) AS a 
GROUP BY id;

回答by Jessé Catrinck

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
GROUP BY c.id

This will return the first data in products (equals limit 1)

这将返回产品中的第一个数据（等于限制 1）

回答by Krab

What about this?

那这个呢？

SELECT c.id, c.title, (SELECT id from products AS p 
                            WHERE c.id = p.category_id 
                            ORDER BY ... 
                            LIMIT 1)
   FROM categories AS c;

回答by la_kal

The With clause would do the trick. Something like this:

With 子句可以解决问题。像这样的东西：

WITH SELECTION AS (SELECT id FROM products LIMIT 1)
SELECT a.id, c.id, c.title FROM selection a JOIN categories c ON (c.id = a.id);

回答by Yaki Klein

When using postgres you can use the DISTINCT ONsyntex to limit the number of columns returned from either table.

使用 postgres 时，您可以使用DISTINCT ON语法来限制从任一表返回的列数。

Here is a sample of the code:

下面是代码示例：

SELECT c.id, c.title, p.id AS product_id, p.title FROM categories AS c JOIN ( SELECT DISTINCT ON(p1.id) id, p1.title, p1.category_id FROM products p1 ) p ON (c.id = p.category_id)
The trick is not to join directly on the table with multiple occurrences of the id, rather, first create a table with only a single occurrence for each id

SELECT c.id, c.title, p.id AS product_id, p.title FROM categories AS c JOIN ( SELECT DISTINCT ON(p1.id) id, p1.title, p1.category_id FROM products p1 ) p ON (c.id = p.category_id)
诀窍不是直接在表上加入多次出现的 id，而是首先创建一个表，每个 id 只出现一次

回答by Mchl

Assuming you want product with MIN()imial value in sortcolumn, it would look something like this.

假设您想要列中具有MIN()imial 值的产品sort，它看起来像这样。

SELECT 
  c.id, c.title, p.id AS product_id, p.title
FROM 
  categories AS c
INNER JOIN (
  SELECT
    p.id, p.category_id, p.title
  FROM
    products AS p
  CROSS JOIN (
    SELECT p.category_id, MIN(sort) AS sort
    FROM products
    GROUP BY category_id
  ) AS sq USING (category_id)
) AS p ON c.id = p.category_id

回答by Matías

Replace the tables with yours:

用您的表格替换表格：

SELECT * FROM works w 
LEFT JOIN 
(SELECT photoPath, photoUrl, videoUrl FROM workmedias LIMIT 1) AS wm ON wm.idWork = w.idWork