当前位置: 动力学知识库 > 问答 > 编程问答 >

How to use Group by in mysql

问题描述:

I'm using this sql query to get the product list from DB.

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

It works fine for me. But when same product is in multiple subcategories. It gives me a new row. I just wanted to avoid this and wants GROUP by with SC.sub_category_name. So when a product is in multiple categories, all the categories should list in same row.

Current

  1. 853 Tops Premium Vermicelli /images/tops/853.png Noodles
  2. 853 Tops Premium Vermicelli /images/tops/853.png Vermicelli

Expecting

  1. 853 Tops Premium Vermicelli /images/tops/853.png Noodles, Vermicelli

网友答案:

You can use GROUP_CONCAT() for that purpose, grouping by SC.sub_category_name and remove the distinct. Something like

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;
分享给朋友:
您可能感兴趣的文章:
随机阅读: