看起来你的核心问题是查询结果里rpt_revenue的revenue和other字段全是NULL，但根据你提供的表数据，这些字段应该是有求和值的。咱们来一步步解决这个问题：

问题根源 #

1. 多余的GROUP BY子句：从你给出的history表数据来看，每一行的ghacct都是唯一的，也就是说每个company + ghacct组合只出现一次。这时候加GROUP BY完全没必要，它不会改变结果，还可能掩盖join环节的问题。
2. 字段类型不匹配（最可能原因）：history.ghacct和rpt_revenue.ghacct的数据类型可能不一致（比如一个是字符串、一个是数值型），导致left join时无法匹配到对应数据，最终返回NULL。

修正后的SQL #

情况1：两个表的ghacct类型一致 #

如果确认ghacct在history和rpt_revenue中类型完全相同，直接用下面的SQL即可：

SELECT 
    history.company,
    history.ghacct,
    COALESCE(rpt_revenue.revenue, 0) AS revenue,
    COALESCE(rpt_revenue.other, 0) AS other,
    COALESCE(guest.stay, 0) AS stay,
    COALESCE(guest.nights, 0) AS nights
FROM history
LEFT JOIN (
    SELECT 
        company,
        COUNT(*) AS stay,
        SUM(nights) AS nights
    FROM guest
    GROUP BY company
) guest ON guest.company = history.company
LEFT JOIN (
    SELECT 
        ghacct,
        SUM(revenue) AS revenue,
        SUM(other) AS other
    FROM rpt_revenue
    GROUP BY ghacct
) rpt_revenue ON rpt_revenue.ghacct = history.ghacct
WHERE history.type = 'c'
ORDER BY history.company ASC;

情况2：ghacct类型不一致 #

如果是类型不匹配导致的join失败，给其中一个字段做类型转换即可（比如把数值型转成字符串）：

SELECT 
    history.company,
    history.ghacct,
    COALESCE(rpt_revenue.revenue, 0) AS revenue,
    COALESCE(rpt_revenue.other, 0) AS other,
    COALESCE(guest.stay, 0) AS stay,
    COALESCE(guest.nights, 0) AS nights
FROM history
LEFT JOIN (
    SELECT 
        company,
        COUNT(*) AS stay,
        SUM(nights) AS nights
    FROM guest
    GROUP BY company
) guest ON guest.company = history.company
LEFT JOIN (
    SELECT 
        ghacct,
        SUM(revenue) AS revenue,
        SUM(other) AS other
    FROM rpt_revenue
    GROUP BY ghacct
) rpt_revenue ON CAST(rpt_revenue.ghacct AS VARCHAR(20)) = history.ghacct
WHERE history.type = 'c'
ORDER BY history.company ASC;

关键优化点 #

• 移除了多余的GROUP BY，让SQL更简洁高效；
• 用COALESCE替代ISNULL（COALESCE是ANSI标准函数，兼容性更好），确保没有匹配数据时返回0而非NULL；
• 解决了可能的类型不匹配问题，保证join条件能正确匹配。

验证结果 #

用你提供的测试数据运行修正后的SQL，会得到你期望的结果：

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