sql 获取当前的全部月份的统计

[vbmscomm]

我有个表Table
有3个字段 ：日期DATE（2009-10-10） ，数量NUM , 品种Type

当我在查询界面上选择年份和品种点查询按钮，要有如下效果

日期 数量 品种
2009-01 100 a
2009-02 100 a
2009-03 100 a
2009-04 100 a
2009-05 100 a
2009-06 0 a
2009-07 100 a
2009-08 100 a
2009-09 0 a
2009-10 100 a
2009-11 0 a
2009-12 100 a

像6月，9月在数据库里是没有数据的，要显示为0

请问这种SQL该怎么写啊？

[jointan]

在TempDB中,连接关闭时,会自动删除

[vbmscomm]

select year(a.date) as Year, b.number as [Month], sum(a.money) as in_money
from BUY_IN a
right join (select number from master..spt_values where type='p' and number between 1 and 12) b
on month(a.date)=b.number
where a.date between '2009-1-1' and '2009-5-10'
group by year(a.date),b.number;


这样写为什么只显示有数据 的月份，没有值的月份就显示不出来了啊？

[vbmscomm]

有人知道吗？

[vbmscomm]

麻烦各位高手进来看看

[jointan]

试试:

SELECT a.Year,b.Month,ISNULL(a.in_money,0)
FROM
(
select year(BUY_IN.date) as [Year], month(BUY_IN.date) as [Month], sum(BUY_IN.money) as in_money  
from BUY_IN
WHERE  BUY_IN.date between '2009-1-1' and '2009-5-10' 
GROUP BY  year(BUY_IN.date),month(BUY_IN.date)
 ) a
  right join (select number from master..spt_values where type='p' and number between 1 and 12) b
  on a.Month=b.number

[jointan]

SELECT a.Year,b.Number,ISNULL(a.in_money,0)
FROM
(
select year(BUY_IN.date) as [Year], month(BUY_IN.date) as [Month], sum(BUY_IN.money) as in_money  
from BUY_IN
WHERE  BUY_IN.date between '2009-1-1' and '2009-5-10' 
GROUP BY  year(BUY_IN.date),month(BUY_IN.date)
 ) a
  right join (select number from master..spt_values where type='p' and number between 1 and 12) b
  on a.Month=b.number

[vbmscomm]

我试一下

[vbmscomm]

GROUP BY year(BUY_IN.date),month(BUY_IN.date)
有语法错误

[jointan]

我没你的表结构和测试数据,你自己调一下吧,差不多就是这种套路

即,把每月的统计数据做为一个子表,与你整出来的12行表进行右联接.不要先联接后统计,要先统计后连接

[jointan]

我这测试是成功的,不会是你改里面的内容时改错了吧

[vbmscomm]

消息 1033，级别 15，状态 1，第 16 行
除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
消息 102，级别 15，状态 1，第 17 行
'b' 附近有语法错误。

[vbmscomm]

外围一个select 和right join (select number from master..spt_values where type='p' and number between 1 and 12) b
on a.Month=b.number


就不行le

[vbmscomm]

我的表结构就是1楼的

[jointan]

create table BUY_IN
(
 [date] datetime,
 [money] money
)

insert buy_in (date,money) values('2009-1-2',1500)


SELECT a.Year,b.Number,ISNULL(a.in_money,0)
FROM
(
select year(BUY_IN.date) as [Year], month(BUY_IN.date) as [Month], sum(BUY_IN.money) as in_money  
from BUY_IN
WHERE  BUY_IN.date between '2009-1-1' and '2009-5-10' 
GROUP BY  year(BUY_IN.date),month(BUY_IN.date)
 ) a
  right join (select number from master..spt_values where type='p' and number between 1 and 12) b
  on a.Month=b.number

这是我的全部代码,找个空库去试下,我在SQL2000下试的

[vbmscomm]

号了，但是比如3月份没数据的 返回的年是NULL，返回的MONTH道士正确的

[jointan]

把第一句改成SELECT 2009,b.Number,ISNULL(a.in_money,0)

就OK了吧

[jointan]

不过使用:
select number from master..spt_values where type='p' and number between 1 and 12

这种语句,的确是个馊方法,如果目标SQL Server拒绝访问master库的话,肯定执行不了

[vbmscomm]

那还有号的方法吗

[vbmscomm]

[Quote=引用 26 楼 jointan 的回复:]
把第一句改成SELECT 2009,b.Number,ISNULL(a.in_money,0)

就OK了吧
[/Quote]

这么改就不是个办法啊

[tfwin2]

我这两天也遇到统计类问题，最后用了一个比较猥琐的办法实现的，看你喜欢不喜欢

select max(n),col1 from
(
select count(*) n ,b.col1 from a,b
where a.id=b.id
group by col1
union
select 0 n,col1 from b
)group by col1;

实现思路给你了，具体代码，你要自己尝试下！