select convert(varchar,时间108) 分钟,count(*) 条数 into #temp from 表 group by convert(varchar,时间,108)
不用放入另一个表,直接查就行了 select * from 表 where convert(varchar(16),date,120)='2003-11-19 15:01'
select count(*) from #temp where dtcol>'2003-11-19 00:00:00' and dtcol<'2003-11-19 00:00:59' group by datediff(second, '2003-11-19 00:00:00',dtcol)小心时间跨度太大会溢出
上面那个错了,看错了应该是分钟select count(*) from #temp where dtcol>@start_datetime and dtcol<@end_datetime group by datediff(minute, @start_datetime,dtcol)
select convert(varchar(16),getdate(),121) as timeA, count(*) into #temp from tableA group by timeA
select convert(varchar(16),tableA.date,121) as timeA, count(*) into #temp from tableA group by timeA
into #temp
from 表
group by convert(varchar,时间,108)
select * from 表 where convert(varchar(16),date,120)='2003-11-19 15:01'
select count(*) from #temp
where dtcol>'2003-11-19 00:00:00' and dtcol<'2003-11-19 00:00:59'
group by datediff(second, '2003-11-19 00:00:00',dtcol)小心时间跨度太大会溢出
where dtcol>@start_datetime and dtcol<@end_datetime
group by datediff(minute, @start_datetime,dtcol)
-------------------- ----------
2003-11-20 17:52:16 NULL
2003-11-20 17:53:36 80
2003-11-20 17:53:38 2
2003-11-20 17:53:39 1
2003-11-20 17:53:42 3
2003-11-20 17:53:43 1
2003-11-20 17:53:43 0
2003-11-20 17:53:43 0
2003-11-20 17:53:43 0
2003-11-20 17:53:44 1
2003-11-20 17:53:44 0
2003-11-20 17:53:44 0
2003-11-20 17:53:45 1
2003-11-20 17:53:45 0
2003-11-20 17:53:45 0
2003-11-20 17:53:45 0
2003-11-20 17:53:45 0
2003-11-20 17:53:46 1
2003-11-20 17:53:46 0
2003-11-20 17:53:46 0
2003-11-20 17:53:46 0
2003-11-20 17:53:46 0
2003-11-20 17:53:47 1
2003-11-20 17:53:47 0
2003-11-20 17:53:47 0
2003-11-20 17:53:47 0
2003-11-20 17:53:48 1
2003-11-20 17:53:48 0
2003-11-20 17:53:48 0
2003-11-20 17:53:48 0
2003-11-20 17:53:55 7
2003-11-20 17:53:55 0
2003-11-20 17:53:55 0
2003-11-20 17:53:55 0
2003-11-20 17:53:56 1
2003-11-20 17:53:56 0
2003-11-20 17:53:56 0
2003-11-20 17:53:56 0
2003-11-20 17:53:56 0
2003-11-20 17:53:57 1
2003-11-20 17:53:57 0
2003-11-20 17:53:57 0
2003-11-20 17:53:57 0
2003-11-20 17:53:57 0
2003-11-20 17:53:58 1
2003-11-20 17:53:58 0
2003-11-20 17:53:58 0
2003-11-20 17:53:58 0
2003-11-20 17:53:58 0
2003-11-20 17:53:59 1
2003-11-20 17:53:59 0
2003-11-20 17:53:59 0
2003-11-20 17:53:59 0
2003-11-20 17:53:59 0
2003-11-20 17:54:12 13
2003-11-20 17:54:13 1
2003-11-20 17:54:14 1
2003-11-20 17:54:14 0
2003-11-20 17:54:15 1
2003-11-20 17:54:15 0
2003-11-20 17:54:16 1
2003-11-20 17:54:16 0
2003-11-20 17:54:26 10
2003-11-20 17:54:26 0
2003-11-20 17:54:26 0
2003-11-20 17:54:26 0
2003-11-20 17:54:27 1
2003-11-20 17:54:27 0
2003-11-20 17:54:27 0
2003-11-20 17:54:27 0
2003-11-20 17:54:27 0
2003-11-20 17:54:28 1
2003-11-20 17:54:28 0
2003-11-20 17:54:28 0
2003-11-20 17:54:28 0
2003-11-20 17:54:28 0
2003-11-20 17:54:29 1
2003-11-20 17:54:29 0
2003-11-20 17:54:29 0
2003-11-20 17:54:29 0
2003-11-20 17:54:36 7
2003-11-20 17:54:37 1
2003-11-20 17:54:37 0
2003-11-20 17:54:38 1
2003-11-20 17:54:38 0
2003-11-20 17:54:38 0
2003-11-20 17:54:38 0
2003-11-20 17:54:39 1
2003-11-20 17:54:39 0
2003-11-20 17:54:39 0
2003-11-20 17:54:39 0
2003-11-20 17:54:39 0
2003-11-20 17:54:40 1
2003-11-20 17:54:40 0
2003-11-20 17:54:40 0
2003-11-20 17:54:40 0
2003-11-20 17:54:41 1
2003-11-20 17:54:41 0
2003-11-20 17:54:41 0
2003-11-20 17:54:41 0
2003-11-20 17:54:42 1
2003-11-20 17:54:42 0
2003-11-20 17:54:42 0
2003-11-20 17:54:42 0
2003-11-20 17:54:42 0
2003-11-20 17:54:43 1
2003-11-20 17:54:43 0
2003-11-20 17:54:43 0
2003-11-20 17:54:43 0
2003-11-20 17:54:43 0
2003-11-20 17:54:44 1
2003-11-20 17:54:44 0
2003-11-20 17:54:44 0
2003-11-20 17:54:44 0
2003-11-20 17:54:44 0
2003-11-20 17:54:48 4
2003-11-20 17:54:48 0
2003-11-20 17:54:49 1
2003-11-20 17:54:49 0
2003-11-20 17:54:49 0
2003-11-20 17:54:49 0
2003-11-20 17:54:49 0
2003-11-20 17:54:50 1
2003-11-20 17:54:50 0
2003-11-20 17:54:56 6
2003-11-20 17:54:56 0
2003-11-20 17:54:56 0
2003-11-20 17:54:56 0
2003-11-20 17:54:56 0
2003-11-20 17:54:57 1
2003-11-20 17:55:03 6
2003-11-20 17:55:03 0
2003-11-20 17:55:03 0 (所影响的行数为 133 行)
按每分钟查
如查出17:54 有多少条纪录 写在另一个表中 时间 2003-11-20 17:54 纪录数19