sql多表联合按月统计全年的数据
我想要的结果是全年1至12月份每个月的数据都显示,没有数据的为0
月份 金额
1 1
2 2
3 0
4 1
5 2
6 2
7 2
8 2
9 2
10 2
11 2
12 0
在网上也找了很多方法,单表能实现,但是多表就实现不到。
select month(a.GoDate) nyf,SUM(Zrs)Zrs,sum(b.Hj)Hj,SUM(Hjws)Hjws,SUM(Hjys)Hjys from CoderTable a,JpddTable b ,SasonTcket c
where a.id=b.DdId and a.JpId=c.id and a.JpId=c.id and '2014'=year(a.GoDate) and c.id=b.JpId group by month(a.GoDate) order by month(a.GoDate) 这是我最开始的查询语句,用这种方法就只能显示有数据的月份,没有数据的月份不能显示。
后面又在网上找了些方法是用一个连接表,但是这也只能实现单表的查询,实现不到多表,select x.Months,isnull(sum(t.Hjws),0),isnull(sum(t.Hj),0)
from XNumber x left join JpddTable t on x.Months=Month(t.Times)
group by x.Months,
相同的此方法也实现不到
select m as [Date], sum(case when datepart(month,AccessDateTime)=m then AccessCount else 0 end ) as [Count]
from Counter c, (select 1 m union all select 2 union all select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9 union all
select 10 union all select 11 union all select 12 ) aawhere @Year=year(AccessDateTime) group by m
------解决方案--------------------
select * from
(
select number from master..spt_values where number between 1 and 12 and type='p'
) a
left join ....
------解决方案--------------------SELECT
*
FROM
(SELECT
number
FROM
master..spt_values
WHERE
number BETWEEN 1 AND 12 AND type='p') a
LEFT JOIN (SELECT