MySQL中使用CONCAT+GROUP_CONCAT+LEFT OUTER JOIN关联多表的问题

嘿，我懂你在处理这三张表关联时的挫败感——多对多的中间表确实容易让人绕晕，尤其是你可能还没把完整的数据写完。先帮你把缺失的部分补全（合理假设了转账表结构和中间表的示例数据），然后给你几种实用的关联查询方案：

完整表结构与初始化数据 #

首先补全所有表的定义和测试数据，方便你直接测试：

-- 规则信息表
CREATE TABLE `rules` ( 
  `id` int, 
  `Name` varchar(10) 
);
INSERT INTO `rules` (`id`, `name`) VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');

-- 中间关联表（规则与转账的多对多映射）
CREATE TABLE `pivot` ( 
  `id_rule` int, 
  `id_transfert` int 
);
-- 补全示例关联数据：规则1关联转账1、2；规则2关联转账2、3；规则3关联转账1
INSERT INTO `pivot` (`id_rule`, `id_transfert`) VALUES (1,1), (1,2), (2,2), (2,3), (3,1);

-- 转账信息表（你未给出，补全基础结构与测试数据）
CREATE TABLE `transfert` ( 
  `id` int, 
  `amount` decimal(10,2), 
  `transfer_date` datetime 
);
INSERT INTO `transfert` (`id`, `amount`, `transfer_date`) VALUES 
(1, 100.00, '2024-01-01 10:00:00'),
(2, 200.00, '2024-01-02 14:30:00'),
(3, 300.00, '2024-01-03 09:15:00');

常用关联查询场景 #

1. 查询所有规则对应的转账信息（含无转账的规则） #

如果想看到所有规则，哪怕没有关联的转账记录（比如规则d），用LEFT JOIN：

SELECT 
  r.id AS rule_id, 
  r.name AS rule_name, 
  t.id AS transfer_id, 
  t.amount, 
  t.transfer_date
FROM rules r
LEFT JOIN pivot p ON r.id = p.id_rule
LEFT JOIN transfert t ON p.id_transfert = t.id;

如果只需要有对应转账的规则，把LEFT JOIN换成INNER JOIN即可。

2. 查询指定规则的所有转账记录（比如规则a） #

加WHERE条件过滤目标规则：

SELECT 
  t.id, 
  t.amount, 
  t.transfer_date
FROM transfert t
INNER JOIN pivot p ON t.id = p.id_transfert
INNER JOIN rules r ON p.id_rule = r.id
WHERE r.name = 'a';

3. 查询每条转账对应的所有规则 #

反过来关联，查看转账记录绑定了哪些规则：

SELECT 
  t.id AS transfer_id, 
  t.amount, 
  r.id AS rule_id, 
  r.name AS rule_name
FROM transfert t
INNER JOIN pivot p ON t.id = p.id_transfert
INNER JOIN rules r ON p.id_rule = r.id;

额外注意事项 #

• 建议给关联字段（rules.id、pivot.id_rule、pivot.id_transfert、transfert.id）添加主键或索引，大幅提升查询效率，尤其是数据量较大的时候。
• 如果中间表存在重复的关联记录，记得用DISTINCT关键字去重，比如：SELECT DISTINCT r.name, t.amount ...。

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