MYSQL group_concat 选择多行

Question

查询:

SELECT `category`.*, 
       Group_concat(DISTINCT `sub_category`.`english_name` ORDER BY 
       `sub_category`.`order_number` 
       ASC) AS `sub_category`, 
       Group_concat(DISTINCT `sub_category`.`id` ORDER BY 
       `sub_category`.`order_number` 
       ASC) AS `sub_category_id`, 
       Group_concat(`sub_category`.`status` ORDER BY 
       `sub_category`.`order_number` ASC) 
            AS `sub_category_status` 
FROM   `category` 
       LEFT JOIN `item_category` 
              ON `item_category`.`category` = `category`.`id` 
       LEFT JOIN `sub_category` 
              ON `sub_category`.`category` = `category`.`id` 
GROUP  BY `category`.`id` 
ORDER  BY `category`.`order_number` ASC 

有问题的线路:

Group_concat(sub_category.status ORDER BY sub_category.order_number ASC)

结果:

(
        [ID] => 22
        [Create_date] => 2017-11-20
        [Created_by] => 0
        [English_name] => Pens & refills
        [Gujarati_name] => પેન અનેર રીફીલ
        [Header] => False
        [Sidebar] => False
        [Order_number] => 2
        [Status] => Close
        [sub_category] => Botteled Ink,Fountain Pens,gel ink rollball pens
        [sub_category_id] => 54,55,56
        [sub_category_status] => Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open,Open
)

正如你所看到的，我在可以使用的地方使用了 DISTINCT。状态为打开或关闭，因此 DISTINCT 在此列中没有帮助。谁能告诉我如何解决这个问题？

Answer 1

您错过了DISTINCT对于 sub_category_status这就是为什么你得到重复值的原因

Group_concat(`sub_category`.`status` ORDER BY 
       `sub_category`.`order_number` ASC) 
            AS `sub_category_status` 

添加DISTINCT对于上面的列也

Group_concat( DISTINCT `sub_category`.`status` ORDER BY 
       `sub_category`.`order_number` ASC) 
            AS `sub_category_status`