MySQL数据库 多表 多条 记录的多个字段合并成一条记录

SELECT
	o.id,
	o.transaction_num,
	o.order_id,
	o.business,
	o.store_code,
	o.order_amount,
	o.order_time,
	o.mobile,
	o.order_status,
	p.payment_num 
FROM
	t_bill_order AS o
	INNER JOIN t_bill_payment AS p ON o.transaction_num = p.transaction_num 
	AND o.order_id = p.order_id 
ORDER BY
	o.id
	

变成

	SELECT
		o.id,
		o.transaction_num,
		o.order_id,
		o.business,
		o.store_code,
		o.order_amount,
		o.order_time,
		o.mobile,
		o.order_status,
		p.payment_num 
	FROM
		t_bill_order AS o
		INNER JOIN t_bill_payment AS p ON o.transaction_num = p.transaction_num 
		AND o.order_id = p.order_id 
GROUP BY
		o.id
ORDER BY
	   o.id
	

GROUP_CONCAT 一定和 GROUP BY 一起用 不是 ORDER BY
中间用 分号隔开 SEPARATOR ‘;’

SELECT
      o.transaction_num,
      o.order_id,
      o.business,
      o.store_code,
      o.order_amount,
      o.order_time,
      o.mobile,
      o.order_status,
      concat(GROUP_CONCAT( DISTINCT IFNULL(p.payment_num,'') SEPARATOR ';' ),",",GROUP_CONCAT( DISTINCT IFNULL(v.payment_num,'') SEPARATOR ';' )) AS payment_num
    FROM
    t_bill_order AS o
    LEFT JOIN t_bill_payment AS p ON o.transaction_num = p.transaction_num AND o.order_id = p.order_id
    LEFT JOIN t_bill_vcp as v     ON o.transaction_num = v.transaction_num AND o.order_id = v.order_id
  where o.order_id like concat('1615207339774143001', '%')
  GROUP BY o.id
     ORDER BY o.order_time desc, o.id desc

p.payment_num 为null 改成 ‘’ 不然sql语句查询会少