如何统计各分支的员工数量（包含无员工的分支）

需求说明 #

统计各分支的员工数量，要求包含无员工的分支，期望结果如下：

branch_name   count
-------------------
SALES           0
LOAN            1
HR              1

给定表结构及测试数据 #

CREATE TABLE EMP_TBL
(
    EMP_ID INT GENERATED BY DEFAULT AS IDENTITY,
    EMP_NAME VARCHAR2(50) NOT NULL,
    PRIMARY KEY (EMP_ID)
);

CREATE TABLE BRANCH_TBL
(
    BRANCH_ID INT GENERATED BY DEFAULT AS IDENTITY,
    BRANCH_NAME VARCHAR2(50) NOT NULL,
    PRIMARY KEY (BRANCH_ID)
);

CREATE TABLE EMP_BRANCH_TBL
(
    EMP_BRANCH_ID INT GENERATED BY DEFAULT AS IDENTITY,
    EMP_ID INT NOT NULL,
    BRANCH_ID INT NOT NULL,
    PRIMARY KEY (EMP_BRANCH_ID),
    FOREIGN KEY (EMP_ID) REFERENCES EMP_TBL(EMP_ID),
    FOREIGN KEY (BRANCH_ID) REFERENCES BRANCH_TBL(BRANCH_ID)    
);

INSERT INTO EMP_TBL (EMP_NAME) VALUES ('MNOP');
INSERT INTO EMP_TBL (EMP_NAME) VALUES ('ABCD');

INSERT INTO BRANCH_TBL (BRANCH_NAME) VALUES ('SALES');
INSERT INTO BRANCH_TBL (BRANCH_NAME) VALUES ('LOAN');
INSERT INTO BRANCH_TBL (BRANCH_NAME) VALUES ('HR');

INSERT INTO EMP_BRANCH_TBL (EMP_ID, BRANCH_ID) VALUES (1, 2);
INSERT INTO EMP_BRANCH_TBL (EMP_ID, BRANCH_ID) VALUES (2, 3);

实现SQL #

SELECT 
    b.BRANCH_NAME AS branch_name,
    COUNT(eb.EMP_ID) AS count
FROM BRANCH_TBL b
LEFT JOIN EMP_BRANCH_TBL eb 
    ON b.BRANCH_ID = eb.BRANCH_ID
GROUP BY b.BRANCH_NAME
ORDER BY b.BRANCH_NAME;

关键逻辑说明 #

• 使用LEFT JOIN以分支表BRANCH_TBL为主表，确保所有分支都被纳入统计，哪怕没有对应的员工关联记录
• COUNT(eb.EMP_ID)会自动忽略NULL值，因此无员工的分支会统计出0（如果用COUNT(*)会得到1，不符合需求）
• GROUP BY b.BRANCH_NAME按分支名称分组，实现各分支的独立统计
• ORDER BY b.BRANCH_NAME保证结果排序和期望输出一致

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