我有个表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该怎么写啊?
有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该怎么写啊?
http://topic.csdn.net/u/20090510/13/e48161d0-f100-4151-8cfc-0895869706bb.html
SQL codeselect 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; 数据库里没有数据的月份就显示不出来
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;
这样写为什么只显示有数据 的月份,没有值的月份就显示不出来了啊?
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
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
有语法错误
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
消息 102,级别 15,状态 1,第 17 行
'b' 附近有语法错误。
on a.Month=b.number
就不行le
(
[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下试的
select number from master..spt_values where type='p' and number between 1 and 12这种语句,的确是个馊方法,如果目标SQL Server拒绝访问master库的话,肯定执行不了
(
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;实现思路给你了,具体代码,你要自己尝试下!