mysql - 合并具有相同名称列的行并连接场合列

Question

我有一个表，其中有许多行具有重复的名称。我想将这些行合并为一行，并连接不同的场合列值，并用逗号 (,) 分隔

因此，在下面的示例中，第 2191 行将具有场合值:圣诞节、光明节，只是因为，并且重复的行将被删除。

ID      name                            url                                                     amazon                                                  image                                                   price   relationship                                    occasion                                                    

2191    Bake & Decorate Cupcake Set     http://www.kqzyfj.com/click-7602811-10971062?url=h...   http://amzn.to/1pi7KO2                                  http://www.lduhtrp.net/image-7602811-10971062?imgu...   0.00    sister, daughter, granddaughter, niece, friend  christmas 
4517    Bake & Decorate Cupcake Set     http://www.kqzyfj.com/click-7602811-10971062?url=h...   http://amzn.to/1pi7KO2                                  http://www.lduhtrp.net/image-7602811-10971062?imgu...   0.00    sister, daughter, granddaughter, niece, friend  hannukah 
5902    Bake & Decorate Cupcake Set     http://www.kqzyfj.com/click-7602811-10971062?url=h...   http://amzn.to/1pi7KO2                                  http://www.lduhtrp.net/image-7602811-10971062?imgu...   0.00    sister, daughter, granddaughter, niece, friend  just because    
1660    Ballerina Treasure Music Box    http://www.amazon.com/gp/product/B000FGKDLK/ref=as...   http://www.amazon.com/gp/product/B000FGKDLK/ref=as...   http://ws-na.amazon-adsystem.com/widgets/q?_encodi...   25.99   sister, daughter, granddaughter, niece, friend  birthday    
2798    Ballerina Treasure Music Box    http://www.amazon.com/gp/product/B000FGKDLK/ref=as...   http://www.amazon.com/gp/product/B000FGKDLK/ref=as...   http://ws-na.amazon-adsystem.com/widgets/q?_encodi...   25.99   sister, daughter, granddaughter, niece, friend  christmas   
5155    Ballerina Treasure Music Box    http://www.amazon.com/gp/product/B000FGKDLK/ref=as...   http://www.amazon.com/gp/product/B000FGKDLK/ref=as...   http://ws-na.amazon-adsystem.com/widgets/q?_encodi...   25.99   sister, daughter, granddaughter, niece, friend  hannukah    

Answer 1

很难准确说出查询是什么，但您想要走的路径是聚合和group_concat():

select min(id) as id, name, group_concat(occasion separator ', ')
from table t
group by name;

目前尚不清楚您可能希望在输出中包含哪些其他列。