如何使用HAVING子句统计员工开户数并找出开户最多的员工

通过关联accounts与employee表，按员工维度分组统计账户数量：

SELECT 
    e.emp_id,
    e.first_name,
    e.last_name,
    COUNT(a.account_id) AS account_count
FROM 
    employee e
LEFT JOIN 
    accounts a ON e.emp_id = a.open_emp_id
GROUP BY 
    e.emp_id, e.first_name, e.last_name
ORDER BY 
    account_count DESC;

• 用LEFT JOIN可包含未开立任何账户的员工（其账户数显示为0）；若仅需统计有开户记录的员工，替换为INNER JOIN即可。

以下两种方法均可处理并列最多的场景：

方法1：先统计最大账户数再匹配员工 #

WITH emp_account_counts AS (
    SELECT 
        e.emp_id,
        e.first_name,
        e.last_name,
        COUNT(a.account_id) AS account_count
    FROM 
        employee e
    LEFT JOIN 
        accounts a ON e.emp_id = a.open_emp_id
    GROUP BY 
        e.emp_id, e.first_name, e.last_name
)
SELECT 
    emp_id,
    first_name,
    last_name,
    account_count
FROM 
    emp_account_counts
WHERE 
    account_count = (SELECT MAX(account_count) FROM emp_account_counts);

方法2：使用窗口函数（推荐，并列场景更直观） #

WITH emp_account_counts AS (
    SELECT 
        e.emp_id,
        e.first_name,
        e.last_name,
        COUNT(a.account_id) AS account_count,
        RANK() OVER(ORDER BY COUNT(a.account_id) DESC) AS rank_num
    FROM 
        employee e
    LEFT JOIN 
        accounts a ON e.emp_id = a.open_emp_id
    GROUP BY 
        e.emp_id, e.first_name, e.last_name
)
SELECT 
    emp_id,
    first_name,
    last_name,
    account_count
FROM 
    emp_account_counts
WHERE 
    rank_num = 1;

• RANK()会给所有账户数并列第一的员工标记为1，确保全部查询出来；若用ROW_NUMBER()仅返回其中一位，不符合需求。

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