MySQL JOIN 连接表上的 LIMIT 1 [英] MySQL JOIN with LIMIT 1 on joined table

问题描述

我想连接两个表，但是table1上的每条记录只能得到table2的1条记录

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

例如:

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 列).

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

我该怎么做?

推荐答案

我会尝试这样的事情:

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

这篇关于MySQL JOIN 连接表上的 LIMIT 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！