mysql查询一对多重复数据拼接字符串

在MySQL中，如果你想要查询一对多的关系并将多个字段值拼接为一个字符串，你可以使用GROUP_CONCAT()函数。以下是一个简单的例子：

假设有两个表：orders（订单表）和order_items（订单项表），它们通过order_id字段关联。你想要查询每个订单的ID以及包含的所有商品名称。

SELECT o.order_id, GROUP_CONCAT(i.product_name SEPARATOR ', ') AS product_names
FROM orders o
JOIN order_items i ON o.order_id = i.order_id
GROUP BY o.order_id;

这个查询将为每个订单生成一个包含商品名称的字符串，商品名称之间用逗号和空格分隔。GROUP BY子句确保每个订单ID只出现一次。

实战

SELECT 
s.send_no,
s.sender_name,
s.status,
s.store_id,
sr.sender_tel,
COUNT(sd.sn),
GROUP_CONCAT(sd.sn SEPARATOR ',') AS product_names
from send_order_t s
LEFT JOIN sender_t sr ON sr.sender_id = s.sender_id
LEFT JOIN send_details_t sd on s.send_id = sd.send_id
where 
(s.store_id = 'mystore001' or s.status = '1')
-- s.send_no = 'DF11111'
GROUP BY send_no

----优化----
SELECT 
send_no,
sender_name,
status,
store_id,
sender_tel,
COUNT(sn),
GROUP_CONCAT(sn SEPARATOR ',') AS product_names
FROM
(
SELECT 
s.send_no as send_no,
s.sender_name as sender_name,
s.status as status,
s.store_id as store_id,
sr.sender_tel as sender_tel,
sd.sn as sn
from send_order_t s
LEFT JOIN sender_t sr ON sr.sender_id = s.sender_id
LEFT JOIN send_details_t sd on s.send_id = sd.send_id
where 
s.store_id = 'mystore001'
-- s.send_no = 'DF11111'

UNION

SELECT 
s.send_no as send_no,
s.sender_name as sender_name,
s.status as status,
s.store_id as store_id,
sr.sender_tel as sender_tel,
sd.sn as sn
from send_order_t s
LEFT JOIN sender_t sr ON sr.sender_id = s.sender_id
LEFT JOIN send_details_t sd on s.send_id = sd.send_id
where 
s.status = '1'
-- s.send_no = 'DF11111'
) AS tt
GROUP BY send_no

unoin1：

unoin2：

合并：

mysql 一对多取值，如何合并重复的字段？-腾讯云开发者社区-腾讯云