表test
ID SHIJIAN
1 2010-10-31
2 2010-11-01
3 2010-11-02
4 2010-11-10
5 2010-11-11
6 2010-11-12
我现在想实现以下功能汇总当前月每个时间3天内的数量,如图,当前为11月查出的结果应该是
时间 数量
2010-11-01 2
2010-11-02 3
2010-11-10 1
2010-11-11 2
2010-11-12 3
希望可以用一条SQL语句实现,各位大侠,这里跪谢了!
ID SHIJIAN
1 2010-10-31
2 2010-11-01
3 2010-11-02
4 2010-11-10
5 2010-11-11
6 2010-11-12
我现在想实现以下功能汇总当前月每个时间3天内的数量,如图,当前为11月查出的结果应该是
时间 数量
2010-11-01 2
2010-11-02 3
2010-11-10 1
2010-11-11 2
2010-11-12 3
希望可以用一条SQL语句实现,各位大侠,这里跪谢了!
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-18 15:38:55 **************
-->*******************************************************
--> 测试数据:test
if object_id('test') is not null
drop table test---->建表
create table test([ID] int,[SHIJIAN] datetime)
insert test
select 1,'2010-10-31' union all
select 2,'2010-11-01' union all
select 3,'2010-11-02' union all
select 4,'2010-11-10' union all
select 5,'2010-11-11' union all
select 6,'2010-11-12'--> 查询结果
SELECT SHIJIAN ,Ct=(select COUNT(1) from test where DATEDIFF(dd,SHIJIAN,t.SHIJIAN)<=3 and ID <=t.ID)
FROM test t
where month(SHIJIAN)=11
--> 删除表格
DROP TABLE test
go
create table [test]([ID] int,[SHIJIAN] datetime)
insert [test]
select 1,'2010-10-31' union all
select 2,'2010-11-01' union all
select 3,'2010-11-02' union all
select 4,'2010-11-10' union all
select 5,'2010-11-11' union all
select 6,'2010-11-12'
-->查询
select
convert(varchar(10),shijian,120) as 时间,
(select count(1) from test where datediff(dd,shijian,t.shijian) between 0 and 2) as 数量
from
test t
where
datediff(mm,shijian,getdate())=0
/**
时间 数量
---------- -----------
2010-11-01 2
2010-11-02 3
2010-11-10 1
2010-11-11 2
2010-11-12 3(5 行受影响)
**/