如何在连接生产工时表时将缺失的实际工时补为0？

我有两张生产相关的表：PlannedHours（生产计划工时表）和ActualHours（实际测量工时表）。当订单为'abc123'时，PlannedHours表数据如下：

ActualHours表存在两种情况：一种包含全部作业的实际工时，另一种仅包含Activity 2的实际工时（因Activity 1在其他地方执行未被测量）。我希望连接两张表后，将缺失的Actual_hrs显示为0，得到目标结果：

我尝试过两段SQL代码，但均未得到预期结果：第一段代码执行后出现重复数据，第二段添加Operation连接条件后缺失了Activity 1的数据，请求正确的SQL实现方案。

尝试的第一段代码：

SELECT
b.[Operation]
b.[Order]
b.[Actual_hrs]
e.[Planned_hrs]
from Actual_hours b
left join Planned_hours e
on b.[Order] = e.[Order]
where b.[Order] = 'abc123'
group by b.[Order], b.[Operation], e.[Planned_hrs]
order by b.[Order] asc

尝试的第二段代码：

SELECT
b.[Operation]
b.[Order]
b.[Actual_hrs]
e.[Planned_hrs]
from Actual_hours b
left join Planned_hours e
on b.[Order] = e.[Order] and b.[Operation] = e.[Operation]
where b.[Order] = 'abc123'
group by b.[Order], b.[Operation], e.[Planned_hrs]
order by b.[Order] asc

• 第一段代码：以ActualHours为主表左连PlannedHours，仅按Order关联会导致同订单下的作业产生笛卡尔积，进而出现重复数据；同时如果ActualHours没有Activity 1的记录，这条数据根本不会被查询出来。
• 第二段代码：同样以ActualHours为主表，当ActualHours缺少Activity 1的记录时，左连后也无法得到该作业的数据，自然会缺失目标行。

要保留所有计划作业的记录，必须以PlannedHours作为主表进行左连接，同时处理缺失的实际工时为0，还要合并同作业的多条实际工时记录（如目标中的2,2）。以下是不同数据库的实现：

MySQL/MariaDB #

SELECT
    p.Operation,
    p.Order,
    p.Planned_hrs,
    COALESCE(GROUP_CONCAT(a.Actual_hrs SEPARATOR ','), '0') AS Actual_hrs
FROM PlannedHours p
LEFT JOIN ActualHours a
    ON p.Order = a.Order AND p.Operation = a.Operation
WHERE p.Order = 'abc123'
GROUP BY p.Operation, p.Order, p.Planned_hrs
ORDER BY p.Order ASC;

SQL Server #

SELECT
    p.Operation,
    p.[Order],
    p.Planned_hrs,
    COALESCE(STRING_AGG(a.Actual_hrs, ','), '0') AS Actual_hrs
FROM PlannedHours p
LEFT JOIN ActualHours a
    ON p.[Order] = a.[Order] AND p.Operation = a.Operation
WHERE p.[Order] = 'abc123'
GROUP BY p.Operation, p.[Order], p.Planned_hrs
ORDER BY p.[Order] ASC;

核心逻辑说明 #

1. 以PlannedHours为主表，确保所有计划作业都能被查询到，即使没有对应的实际工时记录。
2. 通过LEFT JOIN关联ActualHours，关联条件同时包含Order和Operation，避免笛卡尔积。
3. 使用COALESCE函数将缺失的实际工时（NULL值）替换为0。
4. 使用GROUP_CONCAT（MySQL）或STRING_AGG（SQL Server）合并同作业的多条实际工时记录，得到类似2,2的结果。

内容的提问来源于stack exchange，提问作者Grímur Óli Grímsson