如何使用SQL将两个查询结果按FAXDEPT并排展示并补全空值为0

前置修正说明 #

原查询存在别名笔误：主表别名为dd（DOCDATA dd），但后续条件中误用了id作为别名，示例代码已统一修正为dd，避免执行报错。

实现逻辑 #

• 将两个查询分别封装为CTE（公用表达式）：query1对应无额外关联、过滤条件的原查询，query2对应打开注释新增QUEUE表关联、ilc.num = 252过滤条件的查询
• 由于query1的FAXDEPT覆盖范围更大，直接以query1为左表左关联query2即可覆盖所有需要展示的FAXDEPT
• 使用COALESCE函数将query2无匹配记录的数值字段转换为0，符合展示要求

完整实现代码 #

WITH query1 AS (
    select 
        sq.faxdept, 
        count(sq.docid) as total_docs_q1, 
        sum(sq.pages) as total_pages_q1
    from (
        select 
            idp.id as docid, 
            (MAX(idp.pagenum)+1) as pages, 
            ki178.keyvaluechar as faxdept
        from DOCDATA dd
        left join FAXDEPT ki178 on dd.id = ki178.id
        left join IMPORTSOURCE ki228 on dd.id = ki228.id
        left join BATCHINFO kgd105 on dd.id = kgd105.id
        left join PAGEDATA idp on dd.id = idp.id
        where
            dd.datestored > '10/07/2021' 
            and dd.status = 0  
            and (kgd105.kg128 like 'DISC DIP%SJIN%' or  ki228.keyvaluechar like 'SJIN' )
        group by idp.id, ki178.keyvaluechar
    ) as sq
    group by sq.faxdept
),
query2 AS (
    select 
        sq.faxdept, 
        count(sq.docid) as total_docs_q2, 
        sum(sq.pages) as total_pages_q2
    from (
        select 
            idp.id as docid, 
            (MAX(idp.pagenum)+1) as pages, 
            ki178.keyvaluechar as faxdept
        from DOCDATA dd
        left join FAXDEPT ki178 on dd.id = ki178.id
        left join IMPORTSOURCE ki228 on dd.id = ki228.id
        left join BATCHINFO kgd105 on dd.id = kgd105.id
        left join PAGEDATA idp on dd.id = idp.id
        left join QUEUE ilc on dd.id = ilc.id
        where
            dd.datestored > '10/07/2021' 
            and dd.status = 0  
            and ilc.num = 252
            and (kgd105.kg128 like 'DISC DIP%SJIN%' or  ki228.keyvaluechar like 'SJIN' )
        group by idp.id, ki178.keyvaluechar
    ) as sq
    group by sq.faxdept
)
SELECT 
    q1.faxdept as "FAXDEPT",
    q1.total_docs_q1 as "TOTAL DOCUMENTS(Query1)",
    q1.total_pages_q1 as "TOTAL PAGES (Query1)",
    COALESCE(q2.total_docs_q2, 0) as "TOTAL DOCUMENTS(Query2)",
    COALESCE(q2.total_pages_q2, 0) as "TOTAL PAGES (Query2)"
FROM query1 q1
LEFT JOIN query2 q2 ON q1.faxdept = q2.faxdept
ORDER BY q1.faxdept;

兼容说明 #

如果所用数据库不支持CTE语法，可将两个查询直接作为子查询替换到FROM和JOIN语句中，关联逻辑完全一致。
你之前FULL OUTER JOIN调试失败大概率是两个原因：一是原查询的别名错误导致子查询执行报错，二是没有用COALESCE处理NULL值，或者关联字段匹配错误。

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