SQL查询合并多行后，如何让产品参考值换行显示？

嘿，这个需求太常见了！你已经用GROUP_CONCAT把同一订单的商品合并成单条记录，现在要让每个商品条目强制换行，其实只需要调整GROUP_CONCAT的分隔符就行，下面给你具体方案：

1. 调整GROUP_CONCAT的分隔符为换行符 #

在MySQL里，直接把GROUP_CONCAT的分隔符指定为换行符（\n或CHAR(10)，后者是ASCII标准换行符，兼容性更好），就能让每个商品条目单独占一行。

把你原SQL里的GROUP_CONCAT部分：

GROUP_CONCAT( d.product_reference,'|', d.product_quantity) as products_ordered

替换成：

GROUP_CONCAT( CONCAT(d.product_reference,'|',d.product_quantity) SEPARATOR CHAR(10) ) as products_ordered

如果是在HTML场景展示，换行符\n不会生效，就把分隔符换成HTML换行标签<br>：

GROUP_CONCAT( CONCAT(d.product_reference,'|',d.product_quantity) SEPARATOR '<br>' ) as products_ordered

2. 完整修改后的SQL语句 #

下面是替换后的完整查询，直接用就行：

SELECT 
    d.id_order, 
    os.name AS Status, 
    GROUP_CONCAT( CONCAT(d.product_reference,'|',d.product_quantity) SEPARATOR CHAR(10) ) as products_ordered,
    SUM(d.product_quantity*d.product_price) as order_total, 
    SUM(d.product_quantity) as products_qty, 
    SUM(d.product_weight) as products_weight, 
    o.date_upd AS Date, 
    CONCAT_WS(' ', g.firstname, g.lastname) AS Customer, 
    ad.company, 
    ad.address1, 
    ad.address2, 
    ad.postcode, 
    ad.city, 
    ste.name AS state, 
    cl.name AS country, 
    ad.other AS message, 
    ad.phone, 
    ad.phone_mobile, 
    carrier.name AS carrier, 
    gl.name AS group_name 
FROM ps_order_detail d 
LEFT JOIN ps_orders o ON (d.id_order = o.id_order) 
LEFT JOIN ps_customer g ON (o.id_customer = g.id_customer) 
LEFT JOIN ps_address ad ON ( o.id_address_delivery = ad.id_address ) 
LEFT JOIN ps_state ste ON (ste.id_state = ad.id_state) 
LEFT JOIN ps_country_lang cl ON (cl.id_country = ad.id_country) and cl.id_lang = 1 /* use specific lang */ 
LEFT JOIN ps_carrier carrier ON ( carrier.id_carrier = o.id_carrier ) 
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) AND gl.name LIKE 'customer%' and gl.id_lang=1 /* use specific lang */ 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state and os.id_lang=1 ) /* use specific lang */ 
WHERE o.current_state in (2,3) AND (o.date_add > DATE_SUB(now(), INTERVAL 1 DAY)) 
GROUP BY o.id_order 
ORDER BY o.id_order DESC

3. 额外注意事项 #

• 如果订单里商品数量特别多，要留意MySQL的group_concat_max_len限制（默认是1024字符），超过会被截断。可以临时调整会话参数扩大上限：

  SET SESSION group_concat_max_len = 10000; -- 按需设置更大值
  

  也可以在MySQL配置文件里永久修改这个参数。

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