求一sql语句关于分组后的第一条记录

有记录如：
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
说明（平均时间可以将小时转化为分钟再转回来秒可不计算）

大家帮帮忙啊
虽然有点罗唆了

18 个解决方案

#1

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

#2

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

#3

ID          CheckTime
----------- ------------------------------
1           2007-01-10 14:55:00
3           2007-01-11 11:12:00
6           2007-01-13 10:59:00

CountNumber AvgTime
----------- ------------------------------
3           12:22:00

#4

--借用楼上的数据
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
----------- ------------------------------------------------------
1           2007-01-10 14:55:00
3           2007-01-11 11:12:00
6           2007-01-13 10:59:00

(3 件処理されました)

CountNumber AvgTime
----------- ----------------------------------------------------------------
3           12:22:00

(1 件処理されました)
*/

#5

一楼测试不通过
二楼的可否不用临时表实现

#6

不用临时表，把临时表去了放在一个语句中不就可以了

#7

set nocount on
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'

select *  from @a a where not exists(select 1 from @a where datediff(day,checktime,a.checktime)=0 and id<a.id)

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
(
select id,cast('2007-01-01 '+convert(varchar(10),checktime,108) as smalldatetime) y  from (
select *  from @a a where not exists(select 1 from @a where datediff(day,checktime,a.checktime)=0 and id<a.id)
)aa
)bb

#8

搞定了结帐多谢

#9

create table A(ID int, CheckTime smalldatetime)
insert into A select 1 ,'2007-01-10 14:55:00'
union all select 2 ,'2007-01-10 16:17:00'
union all select 4 ,'2007-01-11 11:12:00'
union all select 3 ,'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'

select A.*
  from A ,
       (
          select min(CheckTime) as CheckTime from A a group by convert(char(10),CheckTime,120)
       ) B
where A.CheckTime=B.CheckTime
drop table A

#10

顶！

#11

#12

我只回答第一个
select min(checktime) minchecktime from aaa
group by date(checktime)
请把date 用一个正确的方式表示.

#13

日本松下有一位高官说过：即使我们不去拜靖国神社韩国人也不会买我们的产品，但不管我们再怎样的去拜靖国神社，中国人照样会买我们的产品。----- 一句让中国人从头凉到脚的话。每直接或间接地购买100元日本货，你就： 1.为日本厂家增加40元的毛利收入 2.为日本企业增加了20元的扩张资本 3.为日本*增加了5元的税收收入 4.给日本的所谓自慰队增加了 10颗子弹 5.多印6～8页的篡改历史的教科书和文件 6.送给小犬蠢一狼参拜鬼社的汽油费 **。如果你买日本汽车，将来开上中国街头的日本坦克就是你造的！！ **。如果你买日本橱具，将来射穿你儿子头颅的子弹就是你造的！！ **。如果你买日本电视音响，将来就会在战地喇叭中听到中国人被杀的哀鸣！！！我们没有时间和权力去采取政治行动，我们只能作力所能及的事，拒买日货是我们对付日本人的最好行动，不但简单而且有效．我每天上网必做的一件事就是把此这篇文章贴到可能的地方，只要上网，我至少要贴上10次！！！作为一个有良知的中国人，来和我和许多爱国人士一起并肩战斗吧！！请把此文贴到一切可能的地方，你一定会为你的所作所为而自豪

朋友们，我庆幸我生于80年代，但我更加痛惜我没能在30年代的战场上手刃鬼子。在我们拥有和平幸福安乐生活的今天，请不要忘了我们爷爷那一代所受到的耻辱！！

#14

LS的帖子发错地方了罢?

#15

if exists(select 1 from sysobjects where name='a' and type='u')
drop table a
create table a(ID int not null,CheckTime datetime null)

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'

select *  from a
/*
ID CheckTime
1 2007-01-10 14:55:00.000
2 2007-01-10 16:17:00.000
3 2007-01-11 11:12:00.000
4 2007-01-11 11:30:00.000
5 2007-01-11 11:38:00.000
6 2007-01-13 10:59:00.000
7 2007-01-13 11:24:00.000
*/

--問題一
select a.*  from a join
(
select min(checktime) checktime from a group by convert(varchar(10),checktime,111)
) b on a.checktime=b.checktime
/*
ID CheckTime
1 2007-01-10 14:55:00.000
3 2007-01-11 11:12:00.000
6 2007-01-13 10:59:00.000
*/
--問題一
select count(1) CountNumber,cast(sum(minutes)/count(1)/60 as varchar(2))+':'+cast(sum(minutes)/count(1)%60 as varchar(2))+':00'   AvgTime from
(
select datepart(hh,a.checktime)*60+datepart(mi,a.checktime) minutes  from a join
(
select min(checktime) checktime from a group by convert(varchar(10),checktime,111)
) b on a.checktime=b.checktime
)a
/*
CountNumber AvgTime
3 12:22:00
*/

#16

顶ls的爱国志士！：）

#17

DDDDDDDDDDDDDDDDD

#18

1
select * from a where id in (select min(id) from a group by convert(char(10),CheckTime,120)) as CheckTime )

2
使用相关的聚合函数就能解决

#1

#2

#3

#4

#5