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

最新推荐文章于 2024-08-29 04:07:07 发布

我命由我不由天ℬ

最新推荐文章于 2024-08-29 04:07:07 发布

阅读量2k

点赞数 1

分类专栏：遇到的问题

本文链接：https://blog.csdn.net/qq_37802465/article/details/112684327

版权

遇到的问题专栏收录该内容

11 篇文章 0 订阅

订阅专栏

这篇博客探讨了如何使用SQL语句将MySQL数据库中的多表记录合并成一条，重点关注了GROUP_CONCAT函数与GROUP_BY子句的配合使用，以实现多个支付编号的合并。同时，展示了在处理NULL值时的策略，确保查询结果完整。

摘要由CSDN通过智能技术生成

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