MySQL JOIN 连接表上的 LIMIT 1
我想连接两个表,但是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
相关文章