我正在使用此sql查询从数据库获取产品列表。
SELECT distinct P.product_id, B.brand_name, P.product_name, P.product_description, SC.sub_category_name, P.product_image_path
FROM table_products as P
INNER JOIN table_brands as B
ON P.brand_id = B.brand_id
INNER JOIN table_product_categories as PC
ON P.product_id = PC.product_id
INNER JOIN table_subcategories as SC
ON SC.sub_categories_id = PC.category_id
INNER JOIN table_subcategory_categories as SCC
ON SC.sub_categories_id = SCC.subcategory_id
ORDER BY P.product_id DESC";
这对我来说可以。但是,当同一产品位于多个子类别中时。它给了我新的一行。我只是想避免这种情况,并希望使用SC.sub_category_name进行GROUP。因此,当产品属于多个类别时,所有类别都应在同一行中列出。
当前
853 Tops Premium粉条/images/tops/853.png面条
853 Tops Premium粉条/images/tops/853.png粉条
期待中
853 Tops Premium粉条/images/tops/853.png面条,粉条
最佳答案
为此,您可以使用GROUP_CONCAT()
,按SC.sub_category_name
分组并删除distinct
。就像是
SELECT P.product_id,
B.brand_name,
P.product_name,
P.product_description,
GROUP_CONCAT(SC.sub_category_name) as sub_cat_list,
P.product_image_path
FROM table_products P
INNER JOIN table_brands B
ON P.brand_id = B.brand_id
INNER JOIN table_product_categories PC
ON P.product_id = PC.product_id
INNER JOIN table_subcategories SC
ON SC.sub_categories_id = PC.category_id
INNER JOIN table_subcategory_categories SCC
ON SC.sub_categories_id = SCC.subcategory_id
GROUP BY P.product_id
ORDER BY P.product_id DESC;
关于mysql - 如何在MySQL中使用Group by,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/27586353/