假如用户一个订单有多个商品,如果没有使用GROUP_CONCAT,查询时需要单个字段显示用户购买产品,ID,名称,数量和购买的商品积分,这样非常不方便统计查询
未使用GROUP_CONCAT
使用GROUP_CONCAT可以对以上字段进行拼接,显示方便,比较容易观看效果如下:
语句如下:
separator '|'为拼接符,可以自行替换
SELECT
a.order_id AS 订单编号,
c.user_id AS 用户ID,
c.accept_name AS 收货姓名,
c.create_time AS 购买日期,
GROUP_CONCAT( 'id', b.id, '-名称:', b.NAME, '数量*', a.goods_nums,',获得商品积分:', (a.goods_nums * a.cost_integral),'积分' separator '|') AS '商品明细',
a.is_send AS 收货状态,1为收货2为退货
FROM
s_order_goods AS a
LEFT JOIN s_goods AS b ON a.goods_id = b.id
LEFT JOIN s_order AS c ON a.order_id = c.id
WHERE
a.is_send = 1
AND a.cost_integral > 0
BETWEEN '2020-01-01 00:00:00'
AND
'2021-05-31 23:59:59'
GROUP BY a.order_id;