一时想不起来了
记录是
2009-07-06 15:52:54.740
2009-07-06 15:52:54.741
2009-07-06 15:52:55.253
2009-07-06 15:52:55.477
2009-07-06 15:52:55.650
...要结果是2009-07-06 15:52:54 2
2009-07-06 15:52:55 3谢谢!
记录是
2009-07-06 15:52:54.740
2009-07-06 15:52:54.741
2009-07-06 15:52:55.253
2009-07-06 15:52:55.477
2009-07-06 15:52:55.650
...要结果是2009-07-06 15:52:54 2
2009-07-06 15:52:55 3谢谢!
from T
group by convert(char(19),[date],120)
INSERT TB
SELECT '2009-07-06 15:52:54.740' UNION ALL
SELECT '2009-07-06 15:52:54.741' UNION ALL
SELECT '2009-07-06 15:52:55.253' UNION ALL
SELECT '2009-07-06 15:52:55.477' UNION ALL
SELECT '2009-07-06 15:52:55.650'SELECT CONVERT(VARCHAR(19),COL,120),COUNT(*)
FROM TB
GROUP BY CONVERT(VARCHAR(19),COL,120)DROP TABLE TB
/*
2009-07-06 15:52:54 2
2009-07-06 15:52:55 3
*/
with temp as
(
select convert(char(19),[date],120) 'time',count(*) 'counts'
from T
group by convert(char(19),[date],120)
)
select * from temp
create table [tab]([tdate] datetime)
insert [tab]
select '2009-07-06 15:52:54.740' union all
select '2009-07-06 15:52:54.741' union all
select '2009-07-06 15:52:55.253' union all
select '2009-07-06 15:52:55.477' union all
select '2009-07-06 15:52:55.650'select min(tdate) from [tab]
group by datename(s,tdate)/*
------------------------------------------------------
2009-07-06 15:52:54.740
2009-07-06 15:52:55.253(所影响的行数为 2 行)
*/
select convert(char(19),[datetime],120),count(*)
from 表
group by convert(char(19),[datetime],120)
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-06 16:13:00
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([time1] datetime)
insert [tb]
select '2009-07-06 15:52:54.740' union all
select '2009-07-06 15:52:54.741' union all
select '2009-07-06 15:52:55.253' union all
select '2009-07-06 15:52:55.477' union all
select '2009-07-06 15:52:55.650'
--------------开始查询--------------------------
select CONVERT(VARCHAR(19),time1,120) as time1,count(*) as [sum] from [tb] group by datepart(ss,time1),CONVERT(VARCHAR(19),time1,120)
----------------结果----------------------------
/*time1 sum
------------------- -----------
2009-07-06 15:52:54 2
2009-07-06 15:52:55 3(所影响的行数为 2 行)*/
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-06 16:13:00
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([time1] datetime)
insert [tb]
select '2009-07-06 15:52:54.740' union all
select '2009-07-06 15:52:54.741' union all
select '2009-07-06 15:52:55.253' union all
select '2009-07-06 15:52:55.477' union all
select '2009-07-06 15:52:55.650'
--------------开始查询--------------------------
select convert(varchar(19),time1,120) as time1,count(*) as [sum]
from [tb]
group by datepart(ss,time1),convert(varchar(19),time1,120)
----------------结果----------------------------
/*time1 sum
------------------- -----------
2009-07-06 15:52:54 2
2009-07-06 15:52:55 3(所影响的行数为 2 行)*/