mysql - 合并多行以消除重复，并合并多列

我正在使用mysql，并希望执行以下操作：

对每列取不同的值，如果一条记录存在多个不同的值，则进行串联

     user_id  pagetitle     companyname   Category1                         Category2               Category3
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Audio Visual Equipment  Audio Visual Equipment- Rentals
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Audio Visual Equipment  Audio Visual Equipment- Rentals
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Audio Visual Equipment  Audio Visual Equipment- Rentals
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Audio Visual Equipment  Audio Visual Equipment- Rentals
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Outdoor Media           Advertising- Signs, Neon, Glow Etc
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Outdoor Media           Advertising- Signs, Neon, Glow Etc
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Printing & Printers     Digital Printing
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Printing & Printers     Digital Printing
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Printing & Printers     Digital Printing
     509      Digital Arts  Digital Arts  Advertising, Media & Promotions   Printing & Printers     Flexographic Printing

     user_id  pagetitle     companyname  Category1                       Category2                                                                        Category3
     509      Digital Arts  Digital Arts    Advertising, Media & Promotions Audio Visual Equipment, Outdoor Media, Printing & Printers, Printing & Printers  Audio Visual Equipment- Rentals, Advertising- Signs, Neon, Glow Etc, Digital Printing , Flexographic Printing

And also merge content from multiple columns to 1 eliminating the empty columns.
Address buildingname landmark Locality sublocality City State Country pincode5 Vaniga Valaagam Old Bus Stand Salem Tamilnadu India 636001

As if i use concat() with separators the above also includes empty and include multiple separators.

5, Vaniga Valaagam, , Old Bus Stand, , Salem, Tamilnadu, India - 636001

有人可以提出实现两者的方法吗？

最佳答案

第1部分：

SELECT user_id,
   GROUP_CONCAT(DISTINCT pagetitle SEPARATOR ', ' ) as pagetitle,
   GROUP_CONCAT(DISTINCT companyname SEPARATOR ', ' ) as companyname,
   GROUP_CONCAT(DISTINCT Category1 SEPARATOR ', ' ) as Category1,
   GROUP_CONCAT(DISTINCT Category2 SEPARATOR ', ' ) as Category2,
   GROUP_CONCAT(DISTINCT Category3 SEPARATOR ', ' ) as Category3
FROM some_table
GROUP BY user_id

第2部分：
精简多个字段，省略空字段

SELECT concat(
  IF(field1>'',concat(field1,', '),''),
  IF(field2>'',concat(field2,', '),''),
  IF(field3>'',concat(field3,', '),'')
  ) as something
FROM somewhere

把它放在一起：

SELECT concat(
  IF(user_id>'',concat(user_id,', '),''),
  IF(pagetitle>'',concat(pagetitle,', '),''),
  IF(Category1>'',concat(Category1,', '),''),
  IF(Category2>'',concat(Category2,', '),''),
  IF(Category3>'',concat(Category3,', '),'')
  ) as something
FROM
(
SELECT user_id,
   GROUP_CONCAT(DISTINCT pagetitle SEPARATOR ', ' ) as pagetitle,
   GROUP_CONCAT(DISTINCT companyname SEPARATOR ', ' ) as companyname,
   GROUP_CONCAT(DISTINCT Category1 SEPARATOR ', ' ) as Category1,
   GROUP_CONCAT(DISTINCT Category2 SEPARATOR ', ' ) as Category2,
   GROUP_CONCAT(DISTINCT Category3 SEPARATOR ', ' ) as Category3
FROM some_table
GROUP BY user_id
) nicehomework

关于mysql - 合并多行以消除重复，并合并多列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26732683/