MySQL实现两表结果合并并添加标识字段的问题求助

表结构与数据 #

event表 #

+--------------------------+-------------------+
|id                        | online_payee_id   |   
+--------------------------+-------------------+
| 1                        | 110               |
| 2                        | 330               |
|44 (not in charity table) | 330               |
+--------------------------+-------------------+

charity表 #

+---+------------+------------+
|id | event_id   |  payee_id  |
+---+------------+------------+
| 1 | 1          |    330     |
| 2 | 2          |    330     |
+---+------------+------------+

需求说明 #

将两张表合并，新增flag字段区分三种状态：

• IsEvent：仅在event表中作为收款人（online_payee_id匹配）
• Charity payee：仅在charity表中作为收款人（payee_id匹配）
• Event payee & Charity payee：同时在两张表中作为收款人

期望结果：

+---+------------+----------------------------+
|id | event_id   |    flag                    |
+---+------------+----------------------------+
| 1 | 1          | Charity payee              |
| 1 | 2          | Event payee & Charity payee|
| 2 | 44         | IsEvent                    |
+---+------------+----------------------------+

注：online_payee_id与payee_id为同一类字段，本次仅针对payee_id=330进行处理。

问题：原SQL出现重复结果 #

尝试的SQL语句：

SELECT 
COALESCE(EV.id, EV2.id) AS `id`, 
IF(EV.online_payee_id AND EC.payee_id,"Event payee / Charity payee",  
  IF(EV.online_payee_id,"Event payee","Charity payee")) 
AS `type` 
FROM `payee` AS `P` 
LEFT JOIN `event` AS `EV` ON P.id = EV.online_payee_id 
LEFT JOIN `charity` AS `EC` ON P.id = EC.payee_id
LEFT JOIN `event` AS `EV2` ON EC.event_id = EV2.id 
WHERE (P.id = 330) 
AND (EC.payee_id IS NOT NULL OR EV.online_payee_id IS NOT NULL)

多次LEFT JOIN导致表之间产生笛卡尔积，从而出现重复结果。

正确解决方案 #

思路 #

1. 先获取所有需要处理的event_id集合：包括event表中online_payee_id=330的id，以及charity表中payee_id=330的event_id，用UNION去重。
2. 针对每个event_id，分别左连接event表和charity表，判断该event_id是否在两张表中存在匹配记录。
3. 通过CASE语句生成对应的id和flag字段。

最终SQL #

SELECT
    CASE
        WHEN e.id IS NULL THEN c.id
        ELSE e.id
    END AS id,
    COALESCE(e.id, c.event_id) AS event_id,
    CASE
        WHEN e.id IS NOT NULL AND c.event_id IS NOT NULL THEN 'Event payee & Charity payee'
        WHEN c.event_id IS NOT NULL THEN 'Charity payee'
        ELSE 'IsEvent'
    END AS flag
FROM
    (
        -- 收集所有关联的event_id，去重
        SELECT id AS event_id FROM event WHERE online_payee_id = 330
        UNION
        SELECT event_id FROM charity WHERE payee_id = 330
    ) AS all_events
LEFT JOIN event e ON all_events.event_id = e.id AND e.online_payee_id = 330
LEFT JOIN charity c ON all_events.event_id = c.event_id AND c.payee_id = 330;

结果验证 #

执行上述SQL后，将得到与期望完全一致的结果，且无重复记录。

内容的提问来源于stack exchange，提问作者Vladimir Štus