关于时间查询的sql

[hqs19821108]

金额 时间
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

[win7cc]

4-6天内就是1月2日，3日，4日 ? 那你还不如查询这4天的，6天在哪里？

[pt1314917]

--> 测试数据: [tb]
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

[--小F--]

declare @s datetime
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

[中国风]

use Tempdb
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

[nianran520]

select 
[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

[幸运的意外]

declare @s varchar
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