SQL技术需求：统计员工ID在绩效表两列的出现次数并修正查询语句

表结构 #

表1 employee： #

employee_id, first_name, last_name
     2           John    Appleseed

表2 performance_review： #

employee_id, reviewer_id
     2            1
     2            3
     2            4
     1            2
     3            2 

需求 #

把first_name和last_name合并成姓名输出，同时统计该员工ID在performance_review表的employee_id列里的出现次数（即该员工作为被评价者的次数），以及同一ID在reviewer_id列里的出现次数（即该员工作为评价者的次数）。

示例输出：

姓名               Employee_id统计次数   Received_review统计次数
-------------------------------------------------------------
John Appleseed              3                2

你当前写的错误SQL（无法正常运行） #

SELECT 
    CONCAT([employee_first_name], ' ' , [employee_last_name]) AS employee_full_name, 
    (SELECT COUNT(employee.employee_id) 
     FROM performance_review AS received_review 
     LEFT JOIN performance_review ON employee.employee_id = performance_review.employee_id) AS received_reviews
FROM 
    employee

正确的SQL写法 #

写法一：子查询分别统计（简单直接） #

SELECT
    CONCAT(e.first_name, ' ', e.last_name) AS 姓名,
    -- 统计该员工作为被评价者的次数
    (SELECT COUNT(*) FROM performance_review pr WHERE pr.employee_id = e.employee_id) AS `Employee_id统计次数`,
    -- 统计该员工作为评价者的次数
    (SELECT COUNT(*) FROM performance_review pr WHERE pr.reviewer_id = e.employee_id) AS `Received_review统计次数`
FROM employee e;

写法二：先分组统计再关联（适合数据量大的场景） #

WITH pr_stats AS (
    SELECT
        employee_id,
        COUNT(*) AS emp_count,
        -- 同时统计该员工作为评价者的次数
        (SELECT COUNT(*) FROM performance_review pr2 WHERE pr2.reviewer_id = pr1.employee_id) AS rev_count
    FROM performance_review pr1
    GROUP BY employee_id
)
SELECT
    CONCAT(e.first_name, ' ', e.last_name) AS 姓名,
    -- 用COALESCE处理无数据的情况，返回0而非NULL
    COALESCE(ps.emp_count, 0) AS `Employee_id统计次数`,
    COALESCE(ps.rev_count, 0) AS `Received_review统计次数`
FROM employee e
LEFT JOIN pr_stats ps ON e.employee_id = ps.employee_id;

你的SQL问题点 #

1. 关联逻辑错误：子查询重复引用performance_review表，但未与主表employee正确关联，导致统计的是全表数据而非当前员工的对应数据。
2. 字段名错误：你写的employee_first_name、employee_last_name不存在，实际表字段为first_name、last_name。
3. 统计逻辑混乱：需要分别针对employee_id和reviewer_id列做独立统计，而非做无效的自连接操作。

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