sql查询补全数据解决方案

sql查询补全数据

SQL code

table1 (classid int,pricetime datetime,price money)insert into table1 select 1,'2012-03-01',22insert into table1 select 1,'2012-03-03',20insert into table1 select 1,'2012-03-04',20insert into table1 select 1,'2012-04-06',23insert into table1 select 1,'2012-04-07',32select top 6  year(datetime)years,datepart(mm,datetime) months,sum(money)from table1 group by year(datetime),datepart(mm,datetime) order by years desc,months desc

  --查询出来的结果是
2012 3 62
2012 4 55



而我想要的是从这个月（5月开始）的前6个月的数据。如下
2012 5 0
2012 4 55
2012 3 62
2012 2 0
2012 1 0
2011 12 0


请高手指点！ 看能不能获取这样的数据啊！！

[解决办法]
你另外定义一张年月表，里面要有你需要的所有年月
table1与年月表not in 关联 pricetime只需要前面年月
然后关联的数据与你前面查询的
union all
并起来。
[解决办法]

SQL code

gocreate table table1(classid int,pricetime datetime,price money)goinsert into table1 select 1,'2012-03-01',22insert into table1 select 1,'2012-03-03',20insert into table1 select 1,'2012-03-04',20insert into table1 select 1,'2012-04-06',23insert into table1 select 1,'2012-04-07',32select b.years,b.months,ISNULL(a.price,0) pricefrom(select      top 6       year(pricetime)years,     datepart(mm,pricetime) months,     sum(price) as pricefrom      table1 group by      year(pricetime),datepart(mm,pricetime)order by      years desc,months desc)aright join(select distinctdatepart(yy,dateadd(MM,-number,MAX(pricetime))) as years,datepart(mm,dateadd(MM,-number,MAX(pricetime))) as monthsfrom table1 cross join master..spt_valueswhere number between -1 and 4 --and type='P'group by number)b on a.years=b.years and a.months=b.monthsorder  by1,2/*years    months    price2011    12    0.002012    1    0.002012    2    0.002012    3    62.002012    4    55.002012    5    0.00*/更改了一下 

 

查看更多 下一篇