有记录如：
ID CheckTime
1 2007-01-10 14:55:00
2 2007-01-10 16:17:00
3 2007-01-11 11:12:00
4 2007-01-11 11:30:00
5 2007-01-11 11:38:00
6 2007-01-13 10:59:00
7 2007-01-13 11:24:00
1.现要获得 每天的第一条记录
2.在1的基础上 获得天数，以及时间的平均值
例：
关于问题1 得到的结果应该是:
ID CheckTime
1 2007-01-10 14:55:00
3 2007-01-11 11:12:00
6 2007-01-13 10:59:00
对于问题2
CountNumber AvgTime
3 12:22:00
说明（平均时间可以将小时转化为分钟再转回来 秒可不计算）
大家帮帮忙啊
虽然有点罗唆了
------解决方案--------------------
1。
select A.*
from A,
(
select convert(char(10),CheckTime,120),min(CheckTime) as CheckTime from A group by convert(char(10),CheckTime,120)
) B
where A.CheckTime=B.CheckTime
------解决方案--------------------
declare @a table(ID int, CheckTime smalldatetime)
insert @a select 1 , '2007-01-10 14:55:00 '
union all select 2 , '2007-01-10 16:17:00 '
union all select 3 , '2007-01-11 11:12:00 '
union all select 4 , '2007-01-11 11:30:00 '
union all select 5 , '2007-01-11 11:38:00 '
union all select 6 , '2007-01-13 10:59:00 '
union all select 7 , '2007-01-13 11:24:00 '
1.select * into #tmp from @a a where not exists(select 1 from @a where datediff(day,checktime,a.checktime)=0 and id <a.id)
2.
select id,cast( '2007-01-01 '+convert(varchar(10),checktime,108) as smalldatetime) y into #t from #tmp
select count(1) CountNumber, convert(varchar,dateadd(minute,avg(datediff(minute, '2007-01-01 00:00:00 ',y)), '2007-01-01 00:00:00 '),108) AvgTime from #t
drop table #tmp,#t
------解决方案--------------------
--借用楼上的数据
declare @a table(ID int, CheckTime smalldatetime)
insert @a select 1 , '2007-01-10 14:55:00 '
union all select 2 , '2007-01-10 16:17:00 '
union all select 3 , '2007-01-11 11:12:00 '
union all select 4 , '2007-01-11 11:30:00 '
union all select 5 , '2007-01-11 11:38:00 '
union all select 6 , '2007-01-13 10:59:00 '
union all select 7 , '2007-01-13 11:24:00 '
--1
select ID, CheckTime from @a a where not exists
(
select 1 from @a b where convert(char(10),b.CheckTime,120)=convert(char(10),a.CheckTime,120) and b.id <a.id
)
--2
select CountNumber=count(1),AvgTime=cast(avg(datepart(hour,CheckTime)*60+datepart(minute,CheckTime))/60 as varchar)+ ': '+
cast(avg(datepart(hour,CheckTime)*60+datepart(minute,CheckTime))%60 as varchar)+ ':00 '
from
(
select ID, CheckTime from @a a where not exists
(
select 1 from @a b where convert(char(10),b.CheckTime,120)=convert(char(10),a.CheckTime,120) and b.id <a.id
)
)c
/*
ID CheckTime
----------- ------------------------------------------------------