使用CTE的MySQL查询如何去除重复输出？

我创建了两个CTE，分别从billableTargets表统计员工的目标计费工时总和，从clientbilling表统计员工的已计费工时总和。单独查询billableTargets相关的CTE结果正常，但关联两个CTE后，同公司下的每条计费记录都会重复显示目标工时数据，导致输出结果大量重复。

原SQL代码如下：

WITH
    cb AS(
        SELECT
            clientbilling.id,
            `Date`,
            `Description`,
            `Hours`,
            `Kilometers`,
            `Company`,
            `Date Created`,
            `Employee`,
            SUM(clientbilling.Hours) AS 'Billed'
        FROM
            clientbilling
        JOIN site ON clientbilling.Company = site.siteID
        JOIN users ON clientbilling.Employee = users.id
        WHERE
            MONTH(clientbilling.Date) = MONTH(CURRENT_DATE())
        GROUP BY
            Company,
            Employee
        ORDER BY
            Employee),
    -- second cte
    bt AS(
        SELECT
            billableTargets.id,
            billableTargets.Employee,
            billableTargets.Company,
            SUM(billableTargets.Target) AS 'Goal',
            billableTargets.Date
        FROM
            billableTargets
        WHERE
            MONTH(billableTargets.Date) = MONTH(CURRENT_DATE())
        GROUP BY
            Company,
            Employee)
SELECT
   bt.Company 'Company',
   bt.Employee,
    bt.Goal,
    cb.Billed,
    cb.Date
FROM
    bt
JOIN site ON bt.Company = site.siteID
JOIN users ON bt.Employee = users.id
JOIN cb ON bt.Company = cb.Company
ORDER BY Employee, Company

重复结果示例：

问题原因 #

1. 关联条件不完整：主查询中关联cb和bt时，仅匹配了Company字段，未匹配Employee。这会导致同一家公司下的所有员工目标工时与所有员工的已计费工时交叉关联，产生笛卡尔积，从而出现重复数据。
2. CTE存在冗余非聚合列：cb和bt的SELECT中包含了id、Date等未在GROUP BY中声明的列，这类非聚合列在分组后会返回任意值，不仅不符合SQL规范，还可能干扰关联逻辑。

修正后的SQL代码 #

WITH
    cb AS(
        SELECT
            `Company`,
            `Employee`,
            SUM(clientbilling.Hours) AS 'Billed'
        FROM
            clientbilling
        JOIN site ON clientbilling.Company = site.siteID
        JOIN users ON clientbilling.Employee = users.id
        WHERE
            MONTH(clientbilling.Date) = MONTH(CURRENT_DATE())
        GROUP BY
            Company,
            Employee
    ),
    bt AS(
        SELECT
            `Employee`,
            `Company`,
            SUM(billableTargets.Target) AS 'Goal'
        FROM
            billableTargets
        WHERE
            MONTH(billableTargets.Date) = MONTH(CURRENT_DATE())
        GROUP BY
            Company,
            Employee
    )
SELECT
   bt.Company AS 'Company',
   bt.Employee,
   bt.Goal,
   cb.Billed
FROM
    bt
JOIN site ON bt.Company = site.siteID
JOIN users ON bt.Employee = users.id
LEFT JOIN cb ON bt.Company = cb.Company AND bt.Employee = cb.Employee
ORDER BY Employee, Company

修正说明 #

• 移除了CTE中冗余的非聚合列，确保GROUP BY仅包含分组键和聚合函数，符合SQL标准。
• 关联cb和bt时，同时匹配Company和Employee字段，确保每个员工的目标工时与自己的已计费工时对应，避免交叉重复。
• 改用LEFT JOIN替代JOIN，保证本月无计费记录的员工也能显示其目标工时（Billed字段为NULL），覆盖更多统计场景。

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