如何将无法共存的两条LEFT JOIN结果行合并为一行？

当前查询结果与期望目标 #

当前输出 #

user_id | country | purpose_id
2         CA       NULL
2         NULL     367
2         NG       NULL
2         NULL     368

期望输出 #

user_id | country | purpose_id
2         CA       367
2         NG       368

原SQL语句 #

SELECT
    cs.user_id,
    UPPER(country_term.slug),
    purpose_term.term_id
FROM
    wp_user_services cs
INNER JOIN wp_posts v ON
    v.ID = cs.visa_id AND v.post_status = 'publish'
INNER JOIN wp_term_relationships tr ON
    tr.object_id = v.ID
INNER JOIN wp_term_taxonomy tt ON
    tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy IN('country', 'purpose')
LEFT JOIN wp_terms country_term ON
    tt.term_id = country_term.term_id AND tt.taxonomy = 'country'
LEFT JOIN wp_terms purpose_term ON
    purpose_term.term_id = tt.term_id AND tt.taxonomy = 'purpose'
WHERE
    cs.user_id = 2;

解决方法 #

原SQL的问题在于，通过tt.taxonomy IN('country', 'purpose')关联后，每个分类（国家/目的）会生成独立行，导致同一签证的国家和目的被拆分到不同记录中。以下两种方法可解决该问题：

方法1：拆分查询后关联 #

分别提取每个签证对应的国家和目的，再与主表关联，确保同一签证的信息合并到一行：

SELECT
    cs.user_id,
    c.country,
    p.purpose_id
FROM
    wp_user_services cs
INNER JOIN wp_posts v ON
    v.ID = cs.visa_id AND v.post_status = 'publish'
-- 子查询获取每个签证的国家信息
LEFT JOIN (
    SELECT 
        tr.object_id AS visa_id,
        UPPER(t.slug) AS country
    FROM wp_term_relationships tr
    JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
    JOIN wp_terms t ON tt.term_id = t.term_id
    WHERE tt.taxonomy = 'country'
) c ON v.ID = c.visa_id
-- 子查询获取每个签证的目的ID
LEFT JOIN (
    SELECT 
        tr.object_id AS visa_id,
        t.term_id AS purpose_id
    FROM wp_term_relationships tr
    JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
    JOIN wp_terms t ON tt.term_id = t.term_id
    WHERE tt.taxonomy = 'purpose'
) p ON v.ID = p.visa_id
WHERE
    cs.user_id = 2;

方法2：条件聚合分组 #

通过CASE语句筛选分类，再按用户ID和签证ID分组，用MAX聚合非空值：

SELECT
    cs.user_id,
    MAX(UPPER(CASE WHEN tt.taxonomy = 'country' THEN t.slug END)) AS country,
    MAX(CASE WHEN tt.taxonomy = 'purpose' THEN t.term_id END) AS purpose_id
FROM
    wp_user_services cs
INNER JOIN wp_posts v ON
    v.ID = cs.visa_id AND v.post_status = 'publish'
INNER JOIN wp_term_relationships tr ON
    tr.object_id = v.ID
INNER JOIN wp_term_taxonomy tt ON
    tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy IN('country', 'purpose')
INNER JOIN wp_terms t ON tt.term_id = t.term_id
WHERE
    cs.user_id = 2
GROUP BY cs.user_id, v.ID;

内容的提问来源于stack exchange，提问作者James John