mysql group_concat 使用实例

分手后的思念是犯贱 2022-08-23 14:54 187阅读 0赞

１．　products\_attribute表　保存商品ＩＤ和属性ＩＤ

２．　attribute 　保存属性ＩＤ和属性值（任意多个）

SELECT ＧＲＯＵＰ\_CONCAT(DISTINCT T2.mydesc separator ' , ')AS  desccrption FROM　// 把每一行记录连起来并用空格＋,分割  
(SELECT products\_id,  
 CONCAT\_WS(SPACE(1),attribute.attribute\_name,products\_attribute.attribute\_value) AS mydesc  
 FROM products\_attribute  
LEFT JOIN attribute  
ON attribute.attribute\_id = products\_attribute.attribute\_id) AS T2//表把各种属性种值连起来

group by T2.products\_id //结果按商品ＩＤ分组．

\+----------------------------------------------------------------------------------------------------------------------------------------+  
| desccrption                                                                                                                            |  
\+----------------------------------------------------------------------------------------------------------------------------------------+  
| Price Yes , Weight 1500g , Size 152mm \* 130mm \* 14mm                                                                                   |  
| Size 11 , t 11 , Price 99 , r 11 , Weight 45 , e 11 , p 11 , Size  , w 11 , o dsfdf , q 11 , i 56565 , 2trre 11 , u 11 , abc 11 , y 11 |  
| abc ww                                                                                                                                 |  
| Wang G.H.' Test                                                                                                                        |  
| abc 15                                                                                                                                 |  
\+----------------------------------------------------------------------------------------------------------------------------------------+  
5 rows in set