MySQL使用GROUP_CONCAT查询时分类ID全部合并到首行问题求解

• 你使用的GROUP_CONCAT是聚合函数，没有配套GROUP BY子句时，会将整张表的查询结果聚合为1行，因此所有产品的分类ID都被拼接在了第一行的Kategorie字段中。
• 原SQL中GROUP_CONCAT的排序条件'p.products_id'加了单引号，被MySQL解析为固定字符串，无法实现按产品ID排序的效果。

SELECT
p.products_id as ArtID,
p.products_model as Artikelnummer,
p.products_ean as EAN,
p.products_sort as "Master Sort",
p.products_master_slave_order as "Int. Sort",
d.products_name as Artikelname,
d.products_short_description as Kurzbeschreibung,
d.products_description as Beschreibung,
u.url_text as url_text,
FORMAT(p.products_vpe_value,1) as VPE,
CONCAT(FORMAT(p.products_price,2)," EUR") as "VK Netto",
FORMAT(p.products_weight,3) as Gewicht,
p.products_shippingtime as Lieferzeit,
p.products_image as Bild1,
GROUP_CONCAT(c.categories_id ORDER by p.products_id SEPARATOR ',') as Kategorie,
p.products_status as Status,
p.disabled_shippings as Spedition,
d.products_keywords as "Keyword Suche",
u.meta_title as "Meta Title 55-60",
u.meta_description as "Meta Description 150"
FROM
xt_products p
LEFT JOIN xt_products_description d on p.products_id = d.products_id
LEFT JOIN xt_products_to_categories c on p.products_id = c.products_id
LEFT JOIN xt_seo_url u on p.products_id = u.link_id AND u.link_type = 1
GROUP BY p.products_id

• 删除了原查询中重复定义的p.products_id as ArtID冗余字段
• 移除了GROUP_CONCAT内p.products_id的单引号，排序逻辑正常生效
• 在查询末尾新增GROUP BY p.products_id，按产品ID分组，每个产品对应一行查询结果，GROUP_CONCAT仅拼接当前产品关联的分类ID
  如果运行时报ONLY_FULL_GROUP_BY相关错误，将GROUP BY子句替换为GROUP BY p.products_id, d.products_id, u.link_id即可

内容的提问来源于stack exchange，提问作者Silvio