SQL如何显示一个月的“0"值,如果该月的表中不存在数据 [英] SQL How to show '0' value for a month, if no data exists in the table for that month
问题描述
首先我的结果是这样的:
First of all my result looks like this:
KONTONR | 月 | SELSKAPSKODE | BELOP |
---|---|---|---|
459611 | 1 | BAGA | 156000 |
459611 | 2 | BAGA | 73000 |
459611 | 4 | BAGA | 217000 |
459611 | 5 | BAGA | 136000 |
459611 | 1 | CIVO | 45896 |
459611 | 3 | CIVO | 32498 |
459611 | 4 | CIVO | 9841 |
330096 | 1 | BAGA | 42347 |
330096 | 3 | BAGA | 3695 |
我正在尝试显示多个帐户上 2 个月的预订,每个帐户 (KONTONR
) 有几个代码 (SELSKAPSKODE
) 用于记录预订(预订总和为 BELOP
).我想概述每个帐户 (KONTONR
) 每个月每个代码 (SELSKAPSKODE
) 的预订总额 (BELOP
).我的问题是,如果没有使用该代码进行预订,则该代码在一个月内不会显示.有没有办法来解决这个问题?我理解为什么代码不显示,因为它们根本不在我查询的表中.我怀疑解决方案是制作一个假"表,然后我将其与另一个"表连接(左外连接?).
我只是无法让它工作,我对 SQL 很陌生.有人可以帮忙吗?
我的查询看起来像这样(如果有意义,我只插入了嵌套"查询来设置连接?!):
I'm trying to show month 2 month bookings on several accounts, per account (KONTONR
) there are several codes (SELSKAPSKODE
) on which bookings are recorded (the sum of the bookings as BELOP
). I would like to give an overview of the sum of the bookings (BELOP
) per account (KONTONR
) per month per code (SELSKAPSKODE
). My problem is the codes don't show in a month if no bookings are made on that code. Is there a way to fix this? I understand why the codes don't show, since they're simply not in the table I'm querying. And I suspect that the solution is in making a 'fake' table which I then join (left outer join?) with 'another' table.
I just can't get it to work, I'm pretty new to SQL. Can someone please help?
My query looks like this (I only inserted the 'nested' query to make a set-up for a join, if this makes sense?!):
SELECT TOP (100) PERCENT KONTONR, Month, SELSKAPSKODE, BELOP
FROM (
SELECT SELSKAPSKODE, KONTONR, SKIPS_KODE, MONTH(POSTDATO) AS Month, SUM(BELOP) AS BELOP
FROM dbo.T99_DETALJ
WHERE (POSTDATO >= '2012-01-01') AND (BILAGSART = 0 OR BILAGSART = 2)
GROUP BY SELSKAPSKODE, KONTONR, SKIPS_KODE, MONTH(POSTDATO)
) AS T99_summary
GROUP BY KONTONR, SELSKAPSKODE, Month, BELOP
ORDER BY KONTONR, SELSKAPSKODE, Month
最后,我想填写"缺失的月份(请参阅开头的表格),例如帐户 (KONTONR
) 459611 第 3 个月是缺失".我想显示第 3 个月,预订总和 (BELOP
) 为0".非常感谢任何帮助,提前致谢!
So concluding I would like to 'fill up' the missing months (see table at the start), for instance for account (KONTONR
) 459611 month 3 is 'missing'. I would like to show month 3, with the sum of the bookings (BELOP
) as '0'. Any help is greatly appreciated, thanks in advance!
推荐答案
您可以查询值为 1-12 的表并左外连接您的结果.
You can query a table with the values 1-12 and left outer join your result.
以下示例使用表变量代替查询和 CTE 来构建包含数字的表.
Here is a sample using a table variable instead of your query and a CTE to build a table with numbers.
declare @T table
(
Month int
)
insert into @T values(1)
insert into @T values(1)
insert into @T values(1)
insert into @T values(3)
insert into @T values(3)
;with Months(Month) as
(
select 1
union all
select Month + 1
from Months
where Month < 12
)
select M.Month,
count(T.Month) Count,
isnull(sum(T.Month), 0) Sum
from Months as M
left outer join @T as T
on M.Month = T.Month
group by M.Month
结果:
Month Count Sum
----------- ----------- -----------
1 3 3
2 0 0
3 2 6
4 0 0
5 0 0
6 0 0
7 0 0
8 0 0
9 0 0
10 0 0
11 0 0
12 0 0
这篇关于SQL如何显示一个月的“0"值,如果该月的表中不存在数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!