Mysql根据单个列中的唯一值限制返回结果的数量

I have three tables,

我有三张桌子,

Product_to_categories   => contains two columns (Category_id, Product_id)
Product => contains a few columns (product_id, name, sku, ...)
categories => contains a few columns (category_id, name, ....)

I would like to get a result set for the first 10 unique category_id's joined with the product table, a category can have many product assigned to it, i want all products within one category returned, but i'd like to only get the products from the first 8 categories...

我想得到一个结果集,前10个独特的category_id与产品表一起加入,一个类别可以分配许多产品,我想要退回一个类别中的所有产品,但我只想从中获取产品前8个类别......

Current query:

SELECT p2c.category_id, p.pname, c.category_name
FROM product p LEFT JOIN   product_to_category p2c
ON (p.product_id = p2c.product_id)
Left Join category c (p2c.category_id = c.category) LIMIT 0,8

Current output

catID  |    p.name   |    catName |<br/>
1      |     docs    |      shoe<br/>
1      |     bob      |     shoe<br/>
1      |     mom      |     shoe<br/>
1      |     cat      |     shoe<br/>
1      |     dang     |     shoe<br/>
1      |     kit      |     shoe<br/>
2      |     pis      |     book<br/>
2      |     jiz      |     book<br/>

Currently i only get the first 8 results regardsless, however i am looking to get the following output:

目前我只得到前8个结果无问题,但我希望得到以下输出:

catID  |    p.name   |    catName |<br/>
1      |     docs    |      shoe<br/>
1      |     bob      |     shoe<br/>
1      |     mom      |     shoe<br/>
1      |     cat      |     shoe<br/>
1      |     dang     |     shoe<br/>
1      |     kit      |     shoe<br/>
2      |     pis      |     book<br/>
2      |     jiz      |     book<br/>
3      |     docs     |     shirt<br/>
3      |     bob      |     shirt<br/>
3      |     mom      |     shirt<br/>
4      |     cat      |     light<br/>
4      |     dang     |     light<br/>
5      |     kit      |     sound<br/>
6      |     pis      |     mic<br/>
6      |     jiz      |     mic<br/>
7      |     docs     |     pen<br/>
7      |     bob      |     pen<br/>
7      |     mom      |     pen<br/>
7      |     cat      |     pen<br/>
8      |     dang     |     lace<br/>
8      |     kit      |     lace<br/>
8      |     pis      |     lace<br/>
8      |     jiz      |     lace<br/>

i would like the resultset to contain results of all products that are assigned to the first 8 categories...

我希望结果集包含分配给前8个类别的所有产品的结果...

Please advise. Thanks Hadi

请指教。谢谢哈迪

1 个解决方案

#1

I'm a bit unsure why your query is starting from the product table, so I'm not going to - but aside from this, I think what you need is:

我有点不确定为什么你的查询是从产品表开始的,所以我不打算 - 但除此之外,我认为你需要的是:

SELECT c.category_id, p.pname, c.category_name
FROM category c 
    INNER JOIN product_to_category pc ON c.category_id = pc.category_id
    INNER JOIN product p ON pc.product_id = p.product_id
WHERE c.category_id in (SELECT TOP 8 category_id FROM Category)
ORDER BY 1, 2