金额 时间
100 2009-1-1
300 2009-1-3
200 2009-1-6
100 2009-1-10
500 2009-1-15
300 2009-1-20
500 2009-2-1
350 2009-2-5我想查询出 离2008-12-30日 0-3天内,4-6天内,7-15天内,16-30天内,30天以后
这些区间的金额(比如0-3天内就是30日,31日,1月1日。4-6天内就是1月2日,3日,4日)
0-3天内 4-6天内 7-15天内 16-30天内 30天以后
100 500 300 800 850
100 2009-1-1
300 2009-1-3
200 2009-1-6
100 2009-1-10
500 2009-1-15
300 2009-1-20
500 2009-2-1
350 2009-2-5我想查询出 离2008-12-30日 0-3天内,4-6天内,7-15天内,16-30天内,30天以后
这些区间的金额(比如0-3天内就是30日,31日,1月1日。4-6天内就是1月2日,3日,4日)
0-3天内 4-6天内 7-15天内 16-30天内 30天以后
100 500 300 800 850
if object_id('[tb]') is not null drop table [tb]
create table [tb] (金额 int,时间 datetime)
insert into [tb]
select 100,'2009-1-1' union all
select 300,'2009-1-3' union all
select 200,'2009-1-6' union all
select 100,'2009-1-10' union all
select 500,'2009-1-15' union all
select 300,'2009-1-20' union all
select 500,'2009-2-1' union all
select 350,'2009-2-5'select [0-3天内]=sum(case when 天数 between 0 and 3 then 金额 else 0 end),
[4-6天内]=sum(case when 天数 between 4 and 6 then 金额 else 0 end),
[7-15天内]=sum(case when 天数 between 7 and 15 then 金额 else 0 end),
[16-30天内]=sum(case when 天数 between 16 and 30 then 金额 else 0 end),
[30天以后]=sum(case when 天数>30 then 金额 else 0 end)
from
(select 金额,天数=datediff(dd,'2008-12-30',时间) from tb)a
set @s='2008-12-30'
select
sum(case datediff(dd,@s,时间)>0 and datediff(dd,@s,时间)<=3 then 金额 else '' end) as '0-3天内',
sum(case datediff(dd,@s,时间)>=4 and datediff(dd,@s,时间)<=6 then 金额 else '' end) as '4-6天内',
sum(case datediff(dd,@s,时间)>=7 and datediff(dd,@s,时间)<=15 then 金额 else '' end) as '7-15天内',
sum(case datediff(dd,@s,时间)>=16 and datediff(dd,@s,时间)<=30 then 金额 else '' end) as '16-30天内',
sum(case datediff(dd,@s,时间)>30 then 金额 else '' end) as '30天以后'
from
tb
go
--> -->
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([金额] int,[时间] Datetime)
Insert #1
select 100,'2009-1-1' union all
select 300,'2009-1-3' union all
select 200,'2009-1-6' union all
select 100,'2009-1-10' union all
select 500,'2009-1-15' union all
select 300,'2009-1-20' union all
select 500,'2009-2-1' union all
select 350,'2009-2-5'
Go
Select
sum(case when datediff(d,'2008-12-30',[时间])<3 then [金额] else 0 end) as [0-3天内],
sum(case when datediff(d,'2008-12-30',[时间])between 4 and 6 then [金额] else 0 end) as [4-6天内],
sum(case when datediff(d,'2008-12-30',[时间])between 7 and 15 then [金额] else 0 end) as [7-15天内],
sum(case when datediff(d,'2008-12-30',[时间])between 16 and 30 then [金额] else 0 end) as [16-30天内],
sum(case when datediff(d,'2008-12-30',[时间])>30 then [金额] else 0 end) as [30天以后]
from #1
[0-3天内]=sum(case when datediff(day,'2008-12-30',时间) between 0 and 3 then 金额 else 0 end),
[4-6天内]=sum(case when datediff(day,'2008-12-30',时间) between 4 and 6 then 金额 else 0 end),
[7-15天内]=sum(case when datediff(day,'2008-12-30',时间) between 7 and 15 then 金额 else 0 end),
[16-30天内]=sum(case when datediff(day,'2008-12-30',时间) between 160 and 30 then 金额 else 0 end),
[30天以后]=sum(case when datediff(day,'2008-12-30',时间) >= 30 then 金额 else 0 end)
from tb
set @s='2008/12/30'
select
sum(case datediff(dd,@s,时间)>0 and datediff(dd,@s,时间)<=3 then 金额 else '' end) as '0-3天内',
sum(case datediff(dd,@s,时间)>=4 and datediff(dd,@s,时间)<=6 then 金额 else '' end) as '4-6天内',
sum(case datediff(dd,@s,时间)>=7 and datediff(dd,@s,时间)<=15 then 金额 else '' end) as '7-15天内',
sum(case datediff(dd,@s,时间)>=16 and datediff(dd,@s,时间)<=30 then 金额 else '' end) as '16-30天内',
sum(case datediff(dd,@s,时间)>30 then 金额 else '' end) as '30天以后'
from
tb