PostgreSQL根据ID单查询获取人员及对应子表全字段方案求助

我有一张主表T_PERSONNE，以及三张子表：T_EMPLOYE（员工）、T_PROSPECT（潜在客户）、T_VISITEUR（访客）。主表的PER_CATEG字段标识人员类别："EMP"代表员工，"PROSP"代表潜在客户，"VIS"代表访客。主表存储公共字段，子表仅存各自独有字段，主表与对应子表的ID保持一致。

已知人员ID，如何用单条查询获取该人员的所有主表字段，以及对应类别子表的所有字段？

我尝试用CASE语句，但只能获取子表单个字段，代码如下：

SELECT *,
       CASE WHEN PER_CATEG='EMP' THEN (select * from T_EMPLOYE where EMP_ID = 1)
            WHEN PER_CATEG='PROSP' THEN (select * from T_PROSPECT where PROSP_ID = 1)
            WHEN PER_CATEG='VIS' THEN (select * from T_VISITEUR where VIS_ID = 1)
            ELSE FALSE
       END
FROM T_PERSONNE 
WHERE PER_ID = 1;

报错信息：the subquery must only return one column（子查询必须仅返回一列）

错误原因 #

你之前的写法报错是因为CASE表达式的每个分支只能返回单个值/单个列，但你的子查询select *返回了子表的所有列，违反了这个限制，因此触发了报错。

方案1：LEFT JOIN + 条件映射（通用SQL） #

这种写法兼容所有SQL数据库，通过左连接三个子表，再根据主表的PER_CATEG字段，只保留对应子表的字段值，其他子表字段会显示为NULL：

SELECT
    p.*,
    -- 员工子表字段
    CASE WHEN p.PER_CATEG = 'EMP' THEN e.EMP_FONCTION END AS EMP_FONCTION,
    CASE WHEN p.PER_CATEG = 'EMP' THEN e.EMP_DATE_EMBAUCHE END AS EMP_DATE_EMBAUCHE,
    CASE WHEN p.PER_CATEG = 'EMP' THEN e.EMP_SALAIRE_EURO END AS EMP_SALAIRE_EURO,
    CASE WHEN p.PER_CATEG = 'EMP' THEN e.EMP_EST_DIRECTEUR END AS EMP_EST_DIRECTEUR,
    -- 潜在客户子表字段
    CASE WHEN p.PER_CATEG = 'PROSP' THEN pr.PROSP_FONCTION END AS PROSP_FONCTION,
    CASE WHEN p.PER_CATEG = 'PROSP' THEN pr.PROSP_DATE_EMBAUCHE END AS PROSP_DATE_EMBAUCHE,
    CASE WHEN p.PER_CATEG = 'PROSP' THEN pr.PROSP_SALAIRE_EURO END AS PROSP_SALAIRE_EURO,
    CASE WHEN p.PER_CATEG = 'PROSP' THEN pr.PROSP_NOMBRE_TOTAL_VENTE END AS PROSP_NOMBRE_TOTAL_VENTE,
    CASE WHEN p.PER_CATEG = 'PROSP' THEN pr.PROSP_BENEFICE_TOTAL_VENTE_EURO END AS PROSP_BENEFICE_TOTAL_VENTE_EURO,
    CASE WHEN p.PER_CATEG = 'PROSP' THEN pr.PROSP_EST_DIRECTEUR END AS PROSP_EST_DIRECTEUR,
    -- 访客子表字段
    CASE WHEN p.PER_CATEG = 'VIS' THEN v.VIS_PROFESSION END AS VIS_PROFESSION,
    CASE WHEN p.PER_CATEG = 'VIS' THEN v.VIS_DATE_INSCRIPTION END AS VIS_DATE_INSCRIPTION,
    CASE WHEN p.PER_CATEG = 'VIS' THEN v.VIS_NOMBRE_VISITE END AS VIS_NOMBRE_VISITE,
    CASE WHEN p.PER_CATEG = 'VIS' THEN v.VIS_DATE_DERNIERE_VISITE END AS VIS_DATE_DERNIERE_VISITE,
    CASE WHEN p.PER_CATEG = 'VIS' THEN v.VIS_REVENU_EURO END AS VIS_REVENU_EURO
FROM T_PERSONNE p
LEFT JOIN T_EMPLOYE e ON p.PER_ID = e.EMP_ID
LEFT JOIN T_PROSPECT pr ON p.PER_ID = pr.PROSP_ID
LEFT JOIN T_VISITEUR v ON p.PER_ID = v.VIS_ID
WHERE p.PER_ID = 1;

方案2：PostgreSQL专属 - JSON聚合子表字段 #

如果使用PostgreSQL，可以将对应子表的整行数据转为JSON格式，结果更简洁，便于后续处理：

SELECT
    p.*,
    CASE p.PER_CATEG
        WHEN 'EMP' THEN to_json(e.*)
        WHEN 'PROSP' THEN to_json(pr.*)
        WHEN 'VIS' THEN to_json(v.*)
    END AS category_details
FROM T_PERSONNE p
LEFT JOIN T_EMPLOYE e ON p.PER_ID = e.EMP_ID AND p.PER_CATEG = 'EMP'
LEFT JOIN T_PROSPECT pr ON p.PER_ID = pr.PROSP_ID AND p.PER_CATEG = 'PROSP'
LEFT JOIN T_VISITEUR v ON p.PER_ID = v.VIS_ID AND p.PER_CATEG = 'VIS'
WHERE p.PER_ID = 1;

category_details字段会以JSON格式返回对应子表的所有字段，其他子表因为连接条件带了类别筛选，不会产生多余数据。

方案3：UNION ALL 拼接结果（适合批量查询） #

如果需要查询多个人员，这种写法可以让结果更紧凑，每个人员只返回对应类别的字段：

SELECT
    p.*,
    e.EMP_FONCTION, e.EMP_DATE_EMBAUCHE, e.EMP_SALAIRE_EURO, e.EMP_EST_DIRECTEUR,
    NULL AS PROSP_FONCTION, NULL AS PROSP_DATE_EMBAUCHE, NULL AS PROSP_SALAIRE_EURO,
    NULL AS PROSP_NOMBRE_TOTAL_VENTE, NULL AS PROSP_BENEFICE_TOTAL_VENTE_EURO, NULL AS PROSP_EST_DIRECTEUR,
    NULL AS VIS_PROFESSION, NULL AS VIS_DATE_INSCRIPTION, NULL AS VIS_NOMBRE_VISITE,
    NULL AS VIS_DATE_DERNIERE_VISITE, NULL AS VIS_REVENU_EURO
FROM T_PERSONNE p
JOIN T_EMPLOYE e ON p.PER_ID = e.EMP_ID
WHERE p.PER_ID = 1

UNION ALL

SELECT
    p.*,
    NULL AS EMP_FONCTION, NULL AS EMP_DATE_EMBAUCHE, NULL AS EMP_SALAIRE_EURO, NULL AS EMP_EST_DIRECTEUR,
    pr.PROSP_FONCTION, pr.PROSP_DATE_EMBAUCHE, pr.PROSP_SALAIRE_EURO,
    pr.PROSP_NOMBRE_TOTAL_VENTE, pr.PROSP_BENEFICE_TOTAL_VENTE_EURO, pr.PROSP_EST_DIRECTEUR,
    NULL AS VIS_PROFESSION, NULL AS VIS_DATE_INSCRIPTION, NULL AS VIS_NOMBRE_VISITE,
    NULL AS VIS_DATE_DERNIERE_VISITE, NULL AS VIS_REVENU_EURO
FROM T_PERSONNE p
JOIN T_PROSPECT pr ON p.PER_ID = pr.PROSP_ID
WHERE p.PER_ID = 1

UNION ALL

SELECT
    p.*,
    NULL AS EMP_FONCTION, NULL AS EMP_DATE_EMBAUCHE, NULL AS EMP_SALAIRE_EURO, NULL AS EMP_EST_DIRECTEUR,
    NULL AS PROSP_FONCTION, NULL AS PROSP_DATE_EMBAUCHE, NULL AS PROSP_SALAIRE_EURO,
    NULL AS PROSP_NOMBRE_TOTAL_VENTE, NULL AS PROSP_BENEFICE_TOTAL_VENTE_EURO, NULL AS PROSP_EST_DIRECTEUR,
    v.VIS_PROFESSION, v.VIS_DATE_INSCRIPTION, v.VIS_NOMBRE_VISITE,
    v.VIS_DATE_DERNIERE_VISITE, v.VIS_REVENU_EURO
FROM T_PERSONNE p
JOIN T_VISITEUR v ON p.PER_ID = v.VIS_ID
WHERE p.PER_ID = 1;

因为已知人员ID只会匹配其中一个分支，所以最终只会返回一行数据。

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