MySQL JOIN 在连接表上使用 LIMIT 1

发布于 2024-11-27 01:55:45 字数 320 浏览 1 评论 0原文

我想连接两个表，但 table1 上的每条记录只能获取 table2 的 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 列）。

我该如何去做呢？

原文

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

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 sort column in the products field).

How do I go about doing that?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲影 2024-12-04 01:55:45

我更喜欢类似问题中描述的另一种方法： 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 了，所以，我决定测试我的解决方案的性能（实际上它在 MySQL 和 PostgresSQL 中都能完美运行））以及 @Gravy 在 PostgresQL v.12.9 中提供的解决方案。

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

我在上面运行了查询，只花了13毫秒运行。

在我稍微修改（对于 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;

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

为了捍卫@gravy的解决方案，我同意n+1问题。但是，在这种特殊情况下，产品的数量通常比类别的数量多得多。因此，运行每个类别比运行每个产品（如 @Gravy 的查询）要便宜得多。

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

在简历中，此刻，我的查询是当前任务的性能最佳且最佳的解决方案。

请随意发表评论。

I like more another approach described in a similar question: 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.

For your situation the Query should looks like (this query also will work in PostgresQL and it is pretty fast, see my update below):

This is the fastest query. Use this one.

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. I did the performance test of the query vs other proposed here, and this query is the best option yet!

UPDATE (2022-07-20, PostgresSQL)

I'm not working with mySQL for a while already, so, I decided to test the performance of my solution (which actually works perfect in both MySQL and PostgresQL) with solution provided by @Gravy in PostgresQL v.12.9.

For that I decided to create a dummy tables and data with 100 categories and 100000 products. You can check the code on this gist

I run my query above and it took only 13ms to run.

After I slightly modified (for postgres) the query from @Gravy:

This is slow, do not use it!

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;

and run it too.
It took more than 150ms in my machine. Which is >10x times slower.

In defense of @gravy's solution, I agree with n+1 problem. But, in this particular case, usually the number of products is way larger than categories. So, running through each category is way less expensive than running through each product as in @Gravy's query.

By the way, if your table has 1mln products with 100 categories, the speed of my query is still the same (between 9-17ms), but the query from [@Gravy] takes more than 2 seconds to run

In resume, at this moment, my query is the most performant and optimal solution for the current task.

Feel free to comment.

回复收藏 0 原文

凶凌 2024-12-04 01:55:45

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

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

我的替代解决方案解决了 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;

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.

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.

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

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;

回复收藏 0 原文

另类 2024-12-04 01:55:45

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

这将返回产品中的第一个数据（等于限制 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
GROUP BY c.id

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

回复收藏 0 原文

淡淡绿茶香 2024-12-04 01:55:45

这又如何呢？

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;

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;

回复收藏 0 原文

从﹋此江山别 2024-12-04 01:55:45

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);

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

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);

回复收藏 0 原文

愛放△進行李 2024-12-04 01:55:45

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 = (                               
  SELECT MIN(p1.id) FROM products AS p1
  WHERE c.id=p1.category_id
 )

在子查询中使用 MIN 或 MAX 将使查询运行得更快。

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 = (                               
  SELECT MIN(p1.id) FROM products AS p1
  WHERE c.id=p1.category_id
 )

Using MIN or MAX in a subquery will make your query run much faster.

回复收藏 0 原文

來不及說愛妳 2024-12-04 01:55:45

假设您希望在 sort 列中获得具有 MIN()imial 值的乘积，它看起来像这样。

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

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

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

回复收藏 0 原文

东走西顾 2024-12-04 01:55:45

另一个包含 3 个嵌套表的示例：
1/ 用户
2/ 用户角色公司
3/ Companie

1 用户拥有多个 UserRoleCompanie。
1 UserRoleCompanie 有 1 个用户和 1 个公司
1 Companie 有多个 UserRoleCompanie

SELECT 
u.id as userId, 
u.firstName,
u.lastName,
u.email,
urc.id ,
urc.companieRole,
c.id as companieId,
c.name as companieName
FROM User as u 
JOIN UserRoleCompanie as urc ON u.id = urc.userId
    AND urc.id = (
        SELECT urc2.id
        FROM UserRoleCompanie urc2 
        JOIN Companie ON urc2.companieId = Companie.id
        AND urc2.userId = u.id 
        AND Companie.isPersonal = false
        order by Companie.createdAt DESC
        
        limit 1
    )
    
LEFT JOIN Companie as c ON urc.companieId = c.id

+---------------------------+-----------+--------------------+---------------------------+---------------------------+--------------+---------------------------+-------------------+
| userId                    | firstName | lastName           | email                     | id                        | companieRole | companieId                | companieName      |
+---------------------------+-----------+--------------------+---------------------------+---------------------------+--------------+---------------------------+-------------------+
| cjjt9s9iw037f0748raxmnnde | henry     | pierrot            | [email protected]           | cjtuflye81dwt0748e4hnkiv0 | OWNER        | cjtuflye71dws0748r7vtuqmg | leclerc           |

Another example with 3 nested tables:
1/ User
2/ UserRoleCompanie
3/ Companie

1 user has many UserRoleCompanie.
1 UserRoleCompanie has 1 user and 1 Company
1 Companie has many UserRoleCompanie

SELECT 
u.id as userId, 
u.firstName,
u.lastName,
u.email,
urc.id ,
urc.companieRole,
c.id as companieId,
c.name as companieName
FROM User as u 
JOIN UserRoleCompanie as urc ON u.id = urc.userId
    AND urc.id = (
        SELECT urc2.id
        FROM UserRoleCompanie urc2 
        JOIN Companie ON urc2.companieId = Companie.id
        AND urc2.userId = u.id 
        AND Companie.isPersonal = false
        order by Companie.createdAt DESC
        
        limit 1
    )
    
LEFT JOIN Companie as c ON urc.companieId = c.id

+---------------------------+-----------+--------------------+---------------------------+---------------------------+--------------+---------------------------+-------------------+
| userId                    | firstName | lastName           | email                     | id                        | companieRole | companieId                | companieName      |
+---------------------------+-----------+--------------------+---------------------------+---------------------------+--------------+---------------------------+-------------------+
| cjjt9s9iw037f0748raxmnnde | henry     | pierrot            | [email protected]           | cjtuflye81dwt0748e4hnkiv0 | OWNER        | cjtuflye71dws0748r7vtuqmg | leclerc           |

回复收藏 0 原文

以酷 2024-12-04 01:55:45

在我看来，这是最好的答案（使其具有普遍性）：

SELECT  
TB1.Id  
FROM Table1 AS TB1  
INNER JOIN Table2 AS TB2 ON (TB1.Id = TB2.Id_TB1)  
    AND TB2.Id = (  
        SELECT Id  
        FROM Table2  
        WHERE TB1.Id = Id_TB1  
        ORDER BY Table2.Id DESC  
        LIMIT 1  
    )

In my opinion, this is the best answer (making it general):

SELECT  
TB1.Id  
FROM Table1 AS TB1  
INNER JOIN Table2 AS TB2 ON (TB1.Id = TB2.Id_TB1)  
    AND TB2.Id = (  
        SELECT Id  
        FROM Table2  
        WHERE TB1.Id = Id_TB1  
        ORDER BY Table2.Id DESC  
        LIMIT 1  
    )

回复收藏 0 原文

夜深人未静 2024-12-04 01:55:45

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

以下是代码示例：

SELECT c.id, c.title, p.id AS Product_id, p.title 来自类别 AS c 加入（选择不同的 ON(p1.id) id、p1.title、p1.category_id 来自产品 p1 ) p ON (c.id = p.category_id)
诀窍是不要直接连接多次出现 id 的表，而是首先创建一个每个 id 仅出现一次的表

回复收藏 0 原文

温柔女人霸气范 2024-12-04 01:55:45

将表格替换为您的表格：

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

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

回复收藏 0 原文

浅唱ヾ落雨殇 2024-12-04 01:55:45

我会尝试这样的事情：

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

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

回复收藏 0 原文

~没有更多了~

关于作者

素手挽清风

暂无简介

0 文章

0 评论

25 人气

关注发私信

友情链接

文江博客

MySQL JOIN 在连接表上使用 LIMIT 1

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（12）

关于作者

相关话题

热门标签

推荐作者

隔纱相望

昵称有卵用

梨涡

蓝咒

白芷

樱娆

友情链接

MySQL JOIN 在连接表上使用 LIMIT 1

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（12）

关于作者

相关话题

热门标签

推荐作者

隔纱相望

昵称有卵用

梨涡

蓝咒

白芷

樱娆

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。