员工号相同，取同天日期中最小的一条记录

Question

sid     stime                   etime
002116 2008-11-05 14:13:54.920 2008-11-05 17:09:18.280
002116 2008-11-10 13:02:55.543 2008-11-10 15:54:54.983
002116 2008-11-10 17:10:14.250 2008-11-10 21:10:32.247
002116 2008-11-11 12:58:46.513 2008-11-11 15:47:20.747
002116 2008-11-11 17:09:45.293 2008-11-11 21:11:29.200
002116 2008-11-18 11:34:14.153 2008-11-18 13:17:23.187
002187 2008-11-03 13:01:11.903 2008-11-03 15:46:20.967
002187 2008-11-03 18:17:23.483 2008-11-03 21:10:14.327
002187 2008-11-04 13:01:37.140 2008-11-04 15:32:36.373
002187 2008-11-04 18:19:29.090 2008-11-04 21:10:37.640
002187 2008-11-05 13:03:38.983 2008-11-05 15:29:16.450想得到的记录
002116 2008-11-05 14:13:54.920 2008-11-05 17:09:18.280
002116 2008-11-10 13:02:55.543 2008-11-10 15:54:54.983
002116 2008-11-11 12:58:46.513 2008-11-11 15:47:20.747
002116 2008-11-18 11:34:14.153 2008-11-18 13:17:23.187
002187 2008-11-03 13:01:11.903 2008-11-03 15:46:20.967
002187 2008-11-04 13:01:37.140 2008-11-04 15:32:36.373
002187 2008-11-05 13:03:38.983 2008-11-05 15:29:16.450也就是说员工号相同，取一天中stime最小的一条记录

Answer 1

select 
  *
from
  tb t
where
  not exists(select 1 from tb where sid=t.sid and stime<t.stime)

Answer 2

忘了判断同一天---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([sid] varchar(6),[stime] datetime,[etime] datetime)
insert [tb]
select '002116','2008-11-05 14:13:54.920','2008-11-05 17:09:18.280' union all
select '002116','2008-11-10 13:02:55.543','2008-11-10 15:54:54.983' union all
select '002116','2008-11-10 17:10:14.250','2008-11-10 21:10:32.247' union all
select '002116','2008-11-11 12:58:46.513','2008-11-11 15:47:20.747' union all
select '002116','2008-11-11 17:09:45.293','2008-11-11 21:11:29.200' union all
select '002116','2008-11-18 11:34:14.153','2008-11-18 13:17:23.187' union all
select '002187','2008-11-03 13:01:11.903','2008-11-03 15:46:20.967' union all
select '002187','2008-11-03 18:17:23.483','2008-11-03 21:10:14.327' union all
select '002187','2008-11-04 13:01:37.140','2008-11-04 15:32:36.373' union all
select '002187','2008-11-04 18:19:29.090','2008-11-04 21:10:37.640' union all
select '002187','2008-11-05 13:03:38.983','2008-11-05 15:29:16.450'
 
---查询---
select 
  *
from
  tb t
where
  not exists(select 1 from tb where sid=t.sid and datediff(day,stime,t.stime)=0 and stime<t.stime)---结果---
sid    stime                                                  etime                                                  
------ ------------------------------------------------------ ------------------------------------------------------ 
002116 2008-11-05 14:13:54.920                                2008-11-05 17:09:18.280
002116 2008-11-10 13:02:55.543                                2008-11-10 15:54:54.983
002116 2008-11-11 12:58:46.513                                2008-11-11 15:47:20.747
002116 2008-11-18 11:34:14.153                                2008-11-18 13:17:23.187
002187 2008-11-03 13:01:11.903                                2008-11-03 15:46:20.967
002187 2008-11-04 13:01:37.140                                2008-11-04 15:32:36.373
002187 2008-11-05 13:03:38.983                                2008-11-05 15:29:16.450（所影响的行数为 7 行）

Answer 3

and datediff(day,stime,t.stime)=0没想到用这个条件
结贴