Oracle 11g三表左连接实现单行多列带关联计数查询

当然可行！这种把员工基础信息和关联数据的统计量放在同一行的需求，在Oracle 11g里完全能实现。不过得注意一个坑：如果直接把三张表左连接，因为Employee_Address和Employee_Role都是对Employee_Data的一对多关联，会产生笛卡尔积（比如2个地址×2个角色会生成4条重复的员工记录），所以正确的姿势是先分别统计每个员工的地址数和角色数，再和主表关联。

核心实现思路 #

1. 先对Employee_Address按EMP_ID分组，统计每个员工的地址数量
2. 再对Employee_Role按EMP_ID分组，统计每个员工的角色数量
3. 最后将这两个统计结果通过左连接关联到Employee_Data主表，保证每个员工只返回一行记录

基础统计SQL示例 #

SELECT 
    ed.EMP_ID,
    ed.EMP_NAME, -- 假设Employee_Data包含员工名字字段
    NVL(ea.Emp_Addr_Count, 0) AS Emp_Addr_Count,
    NVL(er.Emp_Role_Count, 0) AS Emp_Role_Count
FROM 
    Employee_Data ed
LEFT JOIN (
    SELECT 
        EMP_ID, 
        COUNT(*) AS Emp_Addr_Count
    FROM Employee_Address
    GROUP BY EMP_ID
) ea ON ed.EMP_ID = ea.EMP_ID
LEFT JOIN (
    SELECT 
        EMP_ID, 
        COUNT(*) AS Emp_Role_Count
    FROM Employee_Role
    GROUP BY EMP_ID
) er ON ed.EMP_ID = er.EMP_ID
WHERE 
    ed.EMP_NAME = 'Mack'; -- 筛选员工Mack的记录

关键细节说明 #

• 使用NVL函数是为了处理无地址/无角色的员工，将统计值显示为0而非NULL，结果更友好
• 子查询中的GROUP BY EMP_ID确保每个员工只返回一行统计结果，彻底避免笛卡尔积问题
• 由于Employee_Data的EMP_ID是唯一的，最终结果中每个员工必然只有一行记录，完全符合你的预期

扩展：同时展示关联数据的具体内容 #

如果需要把多个地址/角色的具体信息也放在同一列（比如用逗号分隔），Oracle 11g支持的LISTAGG函数可以轻松实现：

SELECT 
    ed.EMP_ID,
    ed.EMP_NAME,
    NVL(ea.Emp_Addr_Count, 0) AS Emp_Addr_Count,
    NVL(ea.Address_List, '无') AS Address_List, -- 逗号分隔的地址列表
    NVL(er.Emp_Role_Count, 0) AS Emp_Role_Count,
    NVL(er.Role_List, '无') AS Role_List -- 逗号分隔的角色列表
FROM 
    Employee_Data ed
LEFT JOIN (
    SELECT 
        EMP_ID, 
        COUNT(*) AS Emp_Addr_Count,
        LISTAGG(ADDR_LINE, ', ') WITHIN GROUP (ORDER BY ADDR_ID) AS Address_List -- 假设地址表有ADDR_LINE（地址内容）和ADDR_ID（地址ID）字段
    FROM Employee_Address
    GROUP BY EMP_ID
) ea ON ed.EMP_ID = ea.EMP_ID
LEFT JOIN (
    SELECT 
        EMP_ID, 
        COUNT(*) AS Emp_Role_Count,
        LISTAGG(ROLE_NAME, ', ') WITHIN GROUP (ORDER BY ROLE_ID) AS Role_List -- 假设角色表有ROLE_NAME（角色名称）和ROLE_ID（角色ID）字段
    FROM Employee_Role
    GROUP BY EMP_ID
) er ON ed.EMP_ID = er.EMP_ID
WHERE 
    ed.EMP_NAME = 'Mack';

LISTAGG函数会将分组内的多行字符串合并成一行，用指定的分隔符分隔，完美满足你将多关联记录信息放在同一列的需求。

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