表连接与别名问题：求解公司月度盈亏计算方案

先把你的业务需求拆解清楚：公司每月盈利/亏损 = 特定部门员工带来的总利润 - 所有员工的月度薪资总成本。结合你给出的dept_worker表，我假设还需要另外两张常见业务表来完成计算（你可以根据实际表结构调整）：

• salaries：存储员工薪资信息，结构大概是worker_id, salary, from_date, to_date
• sales（如果利润按销售笔数计算）：存储销售记录，结构大概是worker_id, sale_date, ...

情况1：特定部门每位在职员工每月固定产生1000单位利润 #

这种场景下，我们需要先统计每个月里目标部门（比如你例子里的dept_n=25）的在职员工数量，再乘以1000得到月度总利润；同时统计所有员工当月的薪资总和作为成本，最后做差值计算。

-- 计算每月盈利/亏损
WITH monthly_dept_workers AS (
    -- 统计特定部门每月在职员工数
    SELECT
        DATE_TRUNC('month', dw.from_date) AS month_start,
        COUNT(DISTINCT dw.worker_id) AS dept_worker_count
    FROM dept_worker dw
    WHERE dw.dept_n = 25 -- 指定目标部门
      AND dw.to_date >= DATE_TRUNC('month', CURRENT_DATE) -- 筛选在职或当月在职的员工
    GROUP BY DATE_TRUNC('month', dw.from_date)
),
monthly_salary_cost AS (
    -- 统计每月所有员工的薪资总成本
    SELECT
        DATE_TRUNC('month', s.from_date) AS month_start,
        SUM(s.salary) AS total_salary
    FROM salaries s
    GROUP BY DATE_TRUNC('month', s.from_date)
)
-- 关联两个CTE，计算盈利/亏损
SELECT
    COALESCE(mdw.month_start, msc.month_start) AS month,
    COALESCE(mdw.dept_worker_count * 1000, 0) AS total_profit,
    COALESCE(msc.total_salary, 0) AS total_cost,
    (COALESCE(mdw.dept_worker_count * 1000, 0) - COALESCE(msc.total_salary, 0)) AS profit_or_loss
FROM monthly_dept_workers mdw
FULL OUTER JOIN monthly_salary_cost msc
    ON mdw.month_start = msc.month_start
ORDER BY month;

这里用了表别名（dw代指dept_worker，s代指salaries）简化SQL书写，同时用FULL OUTER JOIN确保不会遗漏只有利润或只有成本的月份。

情况2：特定部门员工每笔销售产生1000单位利润 #

如果利润是按销售笔数计算，那我们需要先统计每月特定部门员工的销售总笔数，再乘以1000得到总利润：

-- 计算每月盈利/亏损（按销售笔数）
WITH monthly_sales_profit AS (
    -- 统计特定部门员工每月销售带来的利润
    SELECT
        DATE_TRUNC('month', sl.sale_date) AS month_start,
        COUNT(sl.sale_id) * 1000 AS total_profit -- 假设sales表有sale_id作为销售记录唯一标识
    FROM sales sl
    JOIN dept_worker dw
        ON sl.worker_id = dw.worker_id
        AND dw.dept_n = 25 -- 关联到特定部门
    GROUP BY DATE_TRUNC('month', sl.sale_date)
),
monthly_salary_cost AS (
    -- 同情况1，统计每月薪资总成本
    SELECT
        DATE_TRUNC('month', s.from_date) AS month_start,
        SUM(s.salary) AS total_salary
    FROM salaries s
    GROUP BY DATE_TRUNC('month', s.from_date)
)
-- 关联计算最终结果
SELECT
    COALESCE(msp.month_start, msc.month_start) AS month,
    COALESCE(msp.total_profit, 0) AS total_profit,
    COALESCE(msc.total_salary, 0) AS total_cost,
    (COALESCE(msp.total_profit, 0) - COALESCE(msc.total_salary, 0)) AS profit_or_loss
FROM monthly_sales_profit msp
FULL OUTER JOIN monthly_salary_cost msc
    ON msp.month_start = msc.month_start
ORDER BY month;

这里用JOIN关联销售表和部门员工表，筛选出特定部门员工的销售记录，再按月份聚合计算利润。

几个注意点 #

• 如果你的薪资表是按天或按周期发放，可能需要调整薪资的月度计算逻辑（比如按在职天数分摊薪资）
• 要注意员工在职时间的筛选，避免统计已经离职的员工的薪资或利润
• 表别名的使用能让多表连接的SQL更简洁易读，建议给每个表起清晰的别名

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