表A
时间 数量
2008-10-29 00:01 2
2008-10-29 00:01 3
2008-10-29 00:03 1如今天从00:01、00:02、00:03.。。
按每分钟来统计2008-10-29 00:01 5
2008-10-29 00:02 2
2008-10-29 00:03 1
。。
时间 数量
2008-10-29 00:01 2
2008-10-29 00:01 3
2008-10-29 00:03 1如今天从00:01、00:02、00:03.。。
按每分钟来统计2008-10-29 00:01 5
2008-10-29 00:02 2
2008-10-29 00:03 1
。。
create table tb(b datetime)
insert into tb select '2007-12-28 20:26:54.480'
insert into tb select '2007-12-28 20:36:54.480'
insert into tb select '2007-12-28 20:46:54.480'
insert into tb select '2007-12-28 20:56:54.480'
insert into tb select '2007-12-28 21:10:54.480'
insert into tb select '2007-12-28 20:24:54.480'
insert into tb select '2007-12-28 21:26:54.480'select
dateadd(mi,(datediff(mi,convert(varchar(10),b,120),dateadd(ss,-1,b))/15)*15,convert(varchar(10),b,120)) as 开始,
dateadd(mi,(datediff(mi,convert(varchar(10),b,120),dateadd(ss,-1,b))/15)*15+15,convert(varchar(10),b,120))as 结束,
count(1) as 数量
from tb
group by
dateadd(mi,(datediff(mi,convert(varchar(10),b,120),dateadd(ss,-1,b))/15)*15,convert(varchar(10),b,120)),
dateadd(mi,(datediff(mi,convert(varchar(10),b,120),dateadd(ss,-1,b))/15)*15+15,convert(varchar(10),b,120))
from a
group by convert(varchar(16),时间,120)
declare @a table (时间 datetime ,数量 int)
insert into @a select '2008-10-29 00:01', 2
union all select '2008-10-29 00:01', 3
union all select '2008-10-29 00:03', 1
select 时间,sum(数量)数量 from @a group by 时间, right(convert(varchar(16) ,时间,120),2)
2008-10-29 00:01:00.000 5
2008-10-29 00:03:00.000 1
时间 数量
2008-10-29 00:01 2
2008-10-29 00:01 3
2008-10-29 00:03 1 如今天从00:01、00:02、00:03.。。
按每分钟来统计 2008-10-29 00:01 5
2008-10-29 00:02 2
2008-10-29 00:03 1
。。 select convert(varchar(16),时间,120) 时间 , sum(数量) 数量 from tb group by convert(varchar(16),时间,120)
from tb
group by 时间这个时间好像是varchar
insert into tb values('2008-10-29 00:01' ,2)
insert into tb values('2008-10-29 00:01' ,3)
insert into tb values('2008-10-29 00:03' ,1)
goselect convert(varchar(16),时间,120) 时间 , sum(数量) 数量 from tb group by convert(varchar(16),时间,120)drop table tb/*
时间 数量
---------------- -----------
2008-10-29 00:01 5
2008-10-29 00:03 1(所影响的行数为 2 行)*/
INSERT @t SELECT '2008-10-29 00:01' ,2
UNION ALL SELECT '2008-10-29 00:01' ,3
UNION ALL SELECT '2008-10-29 00:03', 1
UNION ALL SELECT '2008-10-29 00:13', 1 SELECT TOP 1000 ID=IDENTITY(INT,0,1) INTO # FROM sys.objects a,sys.objects b
--SELECT @@ROWCOUNTSELECT DATEADD(mi,id,mid),SUM(ISNULL(n,0)) FROM #
INNER JOIN
(SELECT MIN(d) mid,MAX(d) mxd FROM @t) b
ON DATEADD(mi,id,mid)<=mxd
LEFT JOIN @t c
ON d=DATEADD(mi,id,mid)
GROUP BY d,DATEADD(mi,id,mid)
/*
2008-10-29 00:01:00.000 5
2008-10-29 00:02:00.000 0
2008-10-29 00:03:00.000 1
2008-10-29 00:04:00.000 0
2008-10-29 00:05:00.000 0
2008-10-29 00:06:00.000 0
2008-10-29 00:07:00.000 0
2008-10-29 00:08:00.000 0
2008-10-29 00:09:00.000 0
2008-10-29 00:10:00.000 0
2008-10-29 00:11:00.000 0
2008-10-29 00:12:00.000 0
2008-10-29 00:13:00.000 1
*/
DROP TABLE #