求1sql语句，取得每分钟的数据

求一sql语句，取得每分钟的数据
一张表，a_Time时间类型 2012-1-13 14:48:14
我现在要取得当前一小时的每分钟的记录条数

该怎么写？select m1,m2,m3.........m60 ???
这样写不可能吧？

谁能写一个？




[解决办法]
datepart和case when行列转换吧
[解决办法]
where datediff(hh,getdate(),a_Time) = 0
[解决办法]

SQL code

select count(*),convert(varchar(120),a_time,120) from tbgroup by convert(varchar(120),a_time,120)
[解决办法]
SQL code
select sum(case when datepart(mi,a_time)=1 then 1 else 0 end) as m1 sum(case when datepart(mi,a_time)=2 then 1 else 0 end) as m2 sum(case when datepart(mi,a_time)=3 then 1 else 0 end) as m3 sum(case when datepart(mi,a_time)=4 then 1 else 0 end) as m4 sum(case when datepart(mi,a_time)=5 then 1 else 0 end) as m5  ............................ sum(case when datepart(mi,a_time)=59 then 1 else 0 end) as m59 sum(case when datepart(mi,a_time)=60 then 1 else 0 end) as m60from tb
[解决办法]
探讨

SQL code
select count(*),convert(varchar(120),a_time,120) from tb
group by convert(varchar(120),a_time,120)

[解决办法]
select count(*),MINUTE(SYSDATE()) from tb group by MINUTE(SYSDATE())           这是My sql 写法
[解决办法]
SQL code
declare @date datetime select @date = '2012-02-01 12:00:00'select dateadd( mi,-number,@date ) from master.dbo.spt_values a where a.type = 'p' and a.number < 60
[解决办法]
小三 大虾的回答应该是正确的
datediff(hour,time,getdate())=0
这就是取当前1小时内的数据的SQL 语句
[解决办法]
select DATEPART (mi, datetransaction), count(*)
from
(
       select datetransaction
       from tabletransaction
       where datetransaction <= getdate() and datetransaction >= dateadd (mi, -60 , getdate())
) z
group by DATEPART( mi, datetransaction )


[解决办法]
select DATEPART (mi, datetransaction), count(*)
from
(
 select datetransaction
 from tabletransaction
 where datetransaction <= getdate() and datetransaction >= dateadd (mi, -59 , getdate())
) z
group by DATEPART( mi, datetransaction )

-59 才对  

[解决办法]
不用写60条啊 用for循环
[解决办法]
不用写60条啊 用for循环
[解决办法]
create table tdate
(
tid int identity(1,1) primary key,
tdate datetime
)
go

insert into tdate  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate() union all  
select getdate()
select count(datepart(mi,ttdate)) as 次数 from tdate where datediff(hh,ttdate,'2012-5-6 8:01:33')=0 group by datepart(mi,ttdate) 
 
[解决办法]
行列转换
1、先用生成一张表，有60行记录。分别是m1,m2,…… m60。(用循环，你懂的)

2、再和时间表进行关联

详情行列转化请参考  
http://blog.csdn.net/t134679/article/details/2032124
[解决办法]
SQL code
create table test(    date datetime,    hight int)insert into test  select getdate(),100union all select getdate(),100union all select getdate(),200union all select getdate(),300union all select getdate(),400union all select getdate(),500---你可以创建函数或者存储过程接收 getate()这个参数declare @datetimestr varchar(20),@datetime datetimeset @datetimestr=convert(varchar(20),getdate(),120)set @datetime=convert(datetime,substring(@datetimestr,0,len(@datetimestr)-5)+':00:00',120)declare @i intset @i=0while @i<60begin---此处只是为了方便，测试输出，你可以包数据整理好，同时输出    select *,datediff(n,@datetime,date) 分 from test where date>=@datetime and datediff(n,@datetime,date) =@i    set @i=@i+1end--------------------date                    hight       分----------------------- ----------- -----------2012-05-06 14:17:47.733 100         172012-05-06 14:17:47.733 200         172012-05-06 14:17:47.733 300         172012-05-06 14:17:47.733 400         172012-05-06 14:17:47.733 500         17(5 行受影响)date                    hight       分----------------------- ----------- -----------2012-05-06 14:18:27.890 100         182012-05-06 14:18:27.890 200         182012-05-06 14:18:27.890 300         182012-05-06 14:18:27.890 400         182012-05-06 14:18:27.890 500         18(5 行受影响)
[解决办法]
SQL code
create table test(    date datetime,    hight int)insert into test  select getdate(),100union all select getdate(),100union all select getdate(),200union all select getdate(),300union all select getdate(),400union all select getdate(),500---你可以创建函数或者存储过程接收 getate()这个参数declare @datetimestr varchar(20),@datetime datetimeset @datetimestr=convert(varchar(20),getdate(),120)set @datetime=convert(datetime,substring(@datetimestr,0,len(@datetimestr)-5)+':00:00',120)declare @i intset @i=0while @i<60begin---此处只是为了方便，测试输出，你可以包数据整理好，同时输出    select *,datediff(n,@datetime,date) 分 from test where date>=@datetime and datediff(n,@datetime,date) =@i    set @i=@i+1end----------------------date                    hight       分----------------------- ----------- -----------2012-05-06 14:17:47.733 100         172012-05-06 14:17:47.733 200         172012-05-06 14:17:47.733 300         172012-05-06 14:17:47.733 400         172012-05-06 14:17:47.733 500         17(5 行受影响)date                    hight       分----------------------- ----------- -----------2012-05-06 14:18:27.890 100         182012-05-06 14:18:27.890 200         182012-05-06 14:18:27.890 300         182012-05-06 14:18:27.890 400         182012-05-06 14:18:27.890 500         18(5 行受影响)