table1 (classid int,pricetime datetime,price money)
insert into table1 select 1,'2012-03-01',22
insert into table1 select 1,'2012-03-03',20
insert into table1 select 1,'2012-03-04',20
insert into table1 select 1,'2012-04-06',23
insert 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
并起来。
go
create table table1(
classid int,
pricetime datetime,
price money
)
go
insert into table1 select 1,'2012-03-01',22
insert into table1 select 1,'2012-03-03',20
insert into table1 select 1,'2012-03-04',20
insert into table1 select 1,'2012-04-06',23
insert into table1 select 1,'2012-04-07',32
select
b.years,b.months,ISNULL(a.price,0) price
from(
select
top 6
year(pricetime)years,
datepart(mm,pricetime) months,
sum(price) as price
from
table1
group by
year(pricetime),datepart(mm,pricetime)
order by
years desc,months desc
)a
right join(
select
datepart(yy,dateadd(MM,-number,MAX(pricetime))) as years,
datepart(mm,dateadd(MM,-number,MAX(pricetime))) as months
from table1 cross join master..spt_values
where number between 0 and 4 and type='P'
group by number)b on a.years=b.years and a.months=b.months
order by
1,2/*
years months price
2011 12 0.00
2012 1 0.00
2012 2 0.00
2012 3 62.00
2012 4 55.00
*/
go
create table table1(
classid int,
pricetime datetime,
price money
)
go
insert into table1 select 1,'2012-03-01',22
insert into table1 select 1,'2012-03-03',20
insert into table1 select 1,'2012-03-04',20
insert into table1 select 1,'2012-04-06',23
insert into table1 select 1,'2012-04-07',32
select
b.years,b.months,ISNULL(a.price,0) price
from(
select
top 6
year(pricetime)years,
datepart(mm,pricetime) months,
sum(price) as price
from
table1
group by
year(pricetime),datepart(mm,pricetime)
order by
years desc,months desc
)a
right join(
select
distinct
datepart(yy,dateadd(MM,-number,MAX(pricetime))) as years,
datepart(mm,dateadd(MM,-number,MAX(pricetime))) as months
from table1 cross join master..spt_values
where number between -1 and 4 --and type='P'
group by number)b on a.years=b.years and a.months=b.months
order by
1,2/*
years months price
2011 12 0.00
2012 1 0.00
2012 2 0.00
2012 3 62.00
2012 4 55.00
2012 5 0.00
*/
更改了一下
Declare @table1 Table(classid int,pricetime datetime,price money)
insert into @table1 select 1,'2012-03-01',22
insert into @table1 select 1,'2012-03-03',20
insert into @table1 select 1,'2012-03-04',20
insert into @table1 select 1,'2012-04-06',23
insert into @table1 select 1,'2012-04-07',32Declare @table Table (datetime datetime)
InSert Into @table
Select '2011-12-01'
Union Select '2012-01-01'
Union Select '2012-02-01'
Union Select '2012-03-01'
Union Select '2012-04-01'
Union Select '2012-05-01'
Union Select '2012-06-01'select top 6 year(datetime)years,datepart(mm,datetime) months,sum(isNull(price,0))
from @table1 Right Join @table On (Convert(Varchar(7),pricetime, 120) = Convert(Varchar(7),datetime, 120))
Where DATEDIFF(mm, datetime, getDate()) Between 0 And 5
group by year(datetime),datepart(mm,datetime)
order by years desc,months desc