PG数据库coalesce或者左联表失效？

Question

[图片]

[图片]
这个sql我已经使用左联字典了，为什么还是无法展示完全的字典项内容（图二展示这个字典项有8个，此处只查出了5个），比如当measure为ABCDE（处）没有数据的话，coalesce好像不会生效，不会让ABCDE（处）对应的这行显示planned_workload和actual_workload为0，而是直接没有这行的数据。
如何修改才能将字典项数据对应的数据行查完全

Answer 1

引自免费微信小程序：皆我百晓生

为了确保所有字典项都能在查询结果中显示，并且填充相应的默认值（例如0），您需要调整您的SQL语句以进行全外连接（FULL OUTER JOIN）。这样可以确保即使在关联表中没有匹配项的情况下，主表中的每一行也会出现在结果集中。

以下是修改后的SQL查询，使用了LEFT JOIN替换JOIN并添加了一个额外的RIGHT JOIN来完成全外连接，然后对缺失的值使用COALESCE函数：

WITH sd_dict_itein_expanded AS (
    SELECT * FROM sd_dict_itein WHERE delflag = 0 AND "name" IN ('ABC measure', 'DEF measure', ...) -- 替换为实际的字典项列表
)
SELECT 
    sdi_expanded."name" AS measure,
    COALESCE(SUM(xcc.planned_workload), 0) AS planned_workload, 
    COALESCE(SUM(xcc.actual_workload), 0) AS actual_workload
FROM 
    sd_dict_itein_expanded sdi_expanded
LEFT JOIN 
    sd_dict_type sdt ON sdt.id = sdi_expanded.type_id AND sdt.code = 'CORROSION CONTROL MEASURE' AND sdt.del_flag = 0
LEFT JOIN 
    NC xco ON xco.measure = sdi_expanded.code
WHERE 
    EXTRACT(YEAR FROM xcc.createtime) = 2023 OR xcc.createtime IS NULL
GROUP BY 
    sdi_expanded."name"
ORDER BY 
    sdi_expanded."name";

在这个查询中：

• 我们首先创建了一个名为sd_dict_itein_expanded的公共表表达式（CTE），其中包含了我们想要确保包含的所有字典项。
• 然后，我们对这个扩展的字典表执行了两个LEFT JOIN操作：第一个是与sdt表的连接，第二个是与xco表的连接。
• 使用OR xcc.createtime IS NULL确保即使xcc表中没有匹配项，也不会排除任何字典项。
• 最后，我们在GROUP BY语句中使用了扩展的字典表的名称，而不是原始的字典表。

这个查询应该会返回一个完整的字典项列表，即使某些字典项在关联表中没有匹配项，也会显示为0。注意，你需要将 'ABC measure', 'DEF measure', ... 替换为实际的字典项列表。