提问人：jchan 提问时间：10/19/2020 最后编辑：Squirreljchan 更新时间：10/19/2020 访问量：357
选择左联接 SQL 语句中显示的空值的总和
Select sum shown null value in left join SQL statement
问：
我有三个表，并期望结果如下，但我不知道如何更正我的sql语句。
select history.company,history.ghacct,rpt_revenue.revenue,rpt_revenue.other, isnull(guest.stay,0) as stay, isnull(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' group by history.company, history.ghacct,rpt_revenue.revenue, rpt_revenue.other,guest.stay,guest.nights order by history.company asc;
历史
ghacct company type
33 JOINT LTD 10010205687 c
3B GLOBAL 10010350619 c
3E FASHION 10010244145 c
3P INT'L 10010112089 c
客人
company stay nights
33 JOINT LTD 01/01/2009 1
33 JOINT LTD 01/06/2009 1
3B GLOBAL 10/02/2019 2
3E FASHION 09/25/2008 6
3P INT'L 08/26/2009 3
3P INT'L 04/26/2010 9
rpt_revenue
ghacct revenue other
10010205687 20 10
10010205687 10 10
10010350619 30 2
10010244145 15 3
10010112089 16 8
10010112089 4 2
结果
company ghacct revenue other stay nights
33 JOINT LTD 10010205687 NULL NULL 2 2
3B GLOBAL 10010350619 NULL NULL 1 2
3E FASHION 10010244145 NULL NULL 1 6
3P INT'L 10010112089 NULL NULL 2 12
预期结果
company ghacct revenue other stay nights
33 JOINT LTD 10010205687 30 20 2 2
3B GLOBAL 10010350619 30 2 1 2
3E FASHION 10010244145 15 3 1 6
3P INT'L 10010112089 20 10 2 12
答：
1赞
Tim Biegeleisen
10/19/2020
#1
我认为您当前查询的主要问题在于该条款，该条款实际上应该只按公司和帐户进行聚合。此外，您可能希望用于收入和其他金额，因为您已经这样做了，用于住宿和房晚。GROUP BYISNULL
SELECT
h.company,
h.ghacct,
ISNULL(rr.revenue, 0) AS revenue,
ISNULL(rr.other, 0) AS other,
ISNULL(g.stay, 0) AS stay,
ISNULL(g.nights, 0) AS nights
FROM history h
LEFT JOIN
(
SELECT company, COUNT(*) AS stay, SUM(nights) AS nights
FROM guest
GROUP BY company
) g
ON g.company = h.company
LEFT JOIN
(
SELECT ghacct, SUM(revenue) AS revenue, SUM(other) AS other
FROM rpt_revenue
GROUP BY ghacct
) rr
ON rr.ghacct = h.ghacct
WHERE
h.type = 'c'
GROUP BY
h.company,
h.ghacct
ORDER BY
h.company;
评论