编写SQL查询选取1000家员工总数达10000的公司

表结构 #

CREATE TABLE company (
    id INT PRIMARY KEY,
    company_name VARCHAR(255),
    address VARCHAR(255),
    telephone VARCHAR(20),
    fax VARCHAR(20)
);
CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    company_id INT,
    address VARCHAR(255),
    telephone VARCHAR(20),
    FOREIGN KEY (company_id) REFERENCES company(id)
);

需求说明 #

现有20000家公司、65000+员工数据，需要查询出1000家公司，且这1000家公司的员工总数之和恰好为10000。原查询仅统计单家公司员工数，无法满足累计求和及数量筛选的要求。

实现思路 #

1. 先统计每家公司的员工数量；
2. 对公司按员工数排序（可按需选择升序/降序），计算累计员工数和累计公司数量；
3. 筛选出累计员工数等于10000且累计公司数为1000的公司集合。

SQL 查询语句 #

方法1：使用CTE和窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

WITH company_employee_count AS (
    -- 统计每家公司的员工数
    SELECT 
        c.id, 
        c.company_name, 
        COUNT(e.id) AS total_employees
    FROM company c
    INNER JOIN employee e ON c.id = e.company_id
    GROUP BY c.id, c.company_name
),
ranked_companies AS (
    -- 计算累计员工数和累计公司数
    SELECT 
        id, 
        company_name, 
        total_employees,
        SUM(total_employees) OVER (ORDER BY total_employees ASC) AS cumulative_employees,
        ROW_NUMBER() OVER (ORDER BY total_employees ASC) AS cumulative_companies
    FROM company_employee_count
)
-- 筛选符合条件的1000家公司
SELECT id, company_name, total_employees
FROM ranked_companies
WHERE cumulative_companies <= 1000
AND cumulative_employees = 10000;

方法2：使用变量累加（适用于MySQL 5.x等不支持窗口函数的数据库） #

SET @cumulative_emp = 0;
SET @cumulative_comp = 0;

SELECT 
    id, 
    company_name, 
    total_employees,
    @cumulative_emp := @cumulative_emp + total_employees AS cumulative_employees,
    @cumulative_comp := @cumulative_comp + 1 AS cumulative_companies
FROM (
    SELECT 
        c.id, 
        c.company_name, 
        COUNT(e.id) AS total_employees
    FROM company c
    INNER JOIN employee e ON c.id = e.company_id
    GROUP BY c.id, c.company_name
    ORDER BY total_employees ASC
) AS company_counts
HAVING cumulative_companies <= 1000 AND cumulative_employees = 10000;

补充说明 #

• 如果按升序排序无法找到完全匹配的组合，可以尝试改为ORDER BY total_employees DESC（降序）重新查询；
• 若不存在恰好1000家公司员工总数为10000的情况，可调整条件为cumulative_employees >= 10000 AND cumulative_companies = 1000，选取最接近需求的结果集。

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