select 员工名 , 日期, max(出门时间) from tb group by 员工名 , 日期
select A.员工名,A.日期,[最晚出门时间]=A.出门时间 from #tab A inner join ( select 员工名, 日期, 出门时间=max(出门时间) from #tab group by 员工名, 日期 )B on a.员工名=b.员工名 and a.日期=b.日期 and a.出门时间=b.出门时间
select 员工名,日期=convert(char(10),日期,120),max(right(convert(char(10),日期,120),8)) 出门时间 from ta group by 员工名,日期=convert(char(10),日期,120),
declare @MyTable table([员工名] varchar(5),[日期] varchar(11),[出门时间] varchar(11)) insert @MyTable select 'Alan','2008-07-08','18:04:00' union all select 'Alan','2008-07-09','09:22:00' union all select 'Alan','2008-07-09','10:29:00' union all select 'Alan','2008-07-09','18:11:00' union all select 'Alan','2008-07-07','23:45:00' union all select 'Alan','2008-07-08','11:31:00' union all select 'Alan','2008-08-08','15:41:00' union all select 'Alan','2008-08-09','11:49:00' union all select 'Alan','2008-12-04','18:59:00' union all select 'nameb','2008-12-04','18:24:00' union all select 'nameb','2008-12-08','09:37:00' union all select 'Ben','2008-07-26','16:00:00' union all select 'Ben','2008-07-26','17:47:00' union all select 'Ben','2008-09-29','10:17:00' union all select 'Ben','2008-09-29','12:43:00' union all select 'Ben','2008-09-29','15:46:00'select [员工名], [日期],max([出门时间]) from @MyTable group by [员工名],[日期] order by [员工名],[日期]--测试结果: /* 员工名 日期 出门时间 Alan 2008-07-07 23:45:00 Alan 2008-07-08 18:04:00 Alan 2008-07-09 18:11:00 Alan 2008-08-08 15:41:00 Alan 2008-08-09 11:49:00 Alan 2008-12-04 18:59:00 Ben 2008-07-26 17:47:00 Ben 2008-09-29 15:46:00 nameb 2008-12-04 18:24:00 nameb 2008-12-08 09:37:00 */
-- ================================================ /* 作者:北方男生(天南地北天涯浪子浪跡天涯,秋去冬來秋水伊人望穿秋水) 时间:2009-01-23 地点:广东東莞 */ -- ================================================= if OBJECT_ID('MyTable ')is not null drop table MyTable go create table MyTable (name nvarchar(10),dt datetime,te datetime) select * from MyTable insert into MyTable values('Alan',' 2008-07-08', '18:04:00') insert into MyTable values('Alan',' 2008-07-09',' 09:22:00 ') insert into MyTable values('Alan',' 2008-07-09',' 10:29:00 ') insert into MyTable values('Alan ','2008-07-09',' 18:11:00 ') insert into MyTable values('Alan',' 2008-07-07',' 23:45:00 ') insert into MyTable values('Alan',' 2008-07-08',' 11:31:00 ') insert into MyTable values('Alan',' 2008-08-08',' 15:41:00 ') insert into MyTable values('Alan',' 2008-08-09',' 11:49:00 ') insert into MyTable values('Alan',' 2008-12-04',' 18:59:00') insert into MyTable values('nameb',' 2008-12-04',' 18:24:00 ') insert into MyTable values('nameb',' 2008-12-08',' 09:37:00 ') insert into MyTable values('Ben',' 2008-07-26',' 16:00:00 ') insert into MyTable values('Ben' ,'2008-07-26 ','17:47:00' ) insert into MyTable values('Ben', '2008-09-29',' 10:17:00' ) insert into MyTable values('Ben', '2008-09-29', '12:43:00' ) insert into MyTable values('Ben', '2008-09-29', '15:46:00' )select name,dt=convert(char(10),dt,120),max(convert(char(10),te,108)) te from MyTable group by MyTable.name,MyTable.dt order by MyTable.namename dt te ---------- ---------- ---------- Alan 2008-07-07 23:45:00 Alan 2008-07-08 18:04:00 Alan 2008-07-09 18:11:00 Alan 2008-08-08 15:41:00 Alan 2008-08-09 11:49:00 Alan 2008-12-04 18:59:00 Ben 2008-07-26 17:47:00 Ben 2008-09-29 15:46:00 nameb 2008-12-04 18:24:00 nameb 2008-12-08 09:37:00 (10 個資料列受到影響)
select A.员工名,A.日期,[最晚出门时间]=A.出门时间 from #tab A
inner join
(
select 员工名, 日期, 出门时间=max(出门时间)
from #tab
group by 员工名, 日期
)B
on a.员工名=b.员工名 and
a.日期=b.日期
and a.出门时间=b.出门时间
from ta
group by 员工名,日期=convert(char(10),日期,120),
declare @MyTable table([员工名] varchar(5),[日期] varchar(11),[出门时间] varchar(11))
insert @MyTable
select 'Alan','2008-07-08','18:04:00' union all
select 'Alan','2008-07-09','09:22:00' union all
select 'Alan','2008-07-09','10:29:00' union all
select 'Alan','2008-07-09','18:11:00' union all
select 'Alan','2008-07-07','23:45:00' union all
select 'Alan','2008-07-08','11:31:00' union all
select 'Alan','2008-08-08','15:41:00' union all
select 'Alan','2008-08-09','11:49:00' union all
select 'Alan','2008-12-04','18:59:00' union all
select 'nameb','2008-12-04','18:24:00' union all
select 'nameb','2008-12-08','09:37:00' union all
select 'Ben','2008-07-26','16:00:00' union all
select 'Ben','2008-07-26','17:47:00' union all
select 'Ben','2008-09-29','10:17:00' union all
select 'Ben','2008-09-29','12:43:00' union all
select 'Ben','2008-09-29','15:46:00'select [员工名], [日期],max([出门时间]) from @MyTable
group by [员工名],[日期]
order by [员工名],[日期]--测试结果:
/*
员工名 日期 出门时间
Alan 2008-07-07 23:45:00
Alan 2008-07-08 18:04:00
Alan 2008-07-09 18:11:00
Alan 2008-08-08 15:41:00
Alan 2008-08-09 11:49:00
Alan 2008-12-04 18:59:00
Ben 2008-07-26 17:47:00
Ben 2008-09-29 15:46:00
nameb 2008-12-04 18:24:00
nameb 2008-12-08 09:37:00
*/
-- ================================================
/*
作者:北方男生(天南地北天涯浪子浪跡天涯,秋去冬來秋水伊人望穿秋水)
时间:2009-01-23
地点:广东東莞
*/
-- =================================================
if OBJECT_ID('MyTable ')is not null drop table MyTable
go
create table MyTable (name nvarchar(10),dt datetime,te datetime)
select * from MyTable
insert into MyTable values('Alan',' 2008-07-08', '18:04:00')
insert into MyTable values('Alan',' 2008-07-09',' 09:22:00 ')
insert into MyTable values('Alan',' 2008-07-09',' 10:29:00 ')
insert into MyTable values('Alan ','2008-07-09',' 18:11:00 ')
insert into MyTable values('Alan',' 2008-07-07',' 23:45:00 ')
insert into MyTable values('Alan',' 2008-07-08',' 11:31:00 ')
insert into MyTable values('Alan',' 2008-08-08',' 15:41:00 ')
insert into MyTable values('Alan',' 2008-08-09',' 11:49:00 ')
insert into MyTable values('Alan',' 2008-12-04',' 18:59:00')
insert into MyTable values('nameb',' 2008-12-04',' 18:24:00 ')
insert into MyTable values('nameb',' 2008-12-08',' 09:37:00 ')
insert into MyTable values('Ben',' 2008-07-26',' 16:00:00 ')
insert into MyTable values('Ben' ,'2008-07-26 ','17:47:00' )
insert into MyTable values('Ben', '2008-09-29',' 10:17:00' )
insert into MyTable values('Ben', '2008-09-29', '12:43:00' )
insert into MyTable values('Ben', '2008-09-29', '15:46:00' )select name,dt=convert(char(10),dt,120),max(convert(char(10),te,108)) te
from MyTable
group by MyTable.name,MyTable.dt
order by MyTable.namename dt te
---------- ---------- ----------
Alan 2008-07-07 23:45:00
Alan 2008-07-08 18:04:00
Alan 2008-07-09 18:11:00
Alan 2008-08-08 15:41:00
Alan 2008-08-09 11:49:00
Alan 2008-12-04 18:59:00
Ben 2008-07-26 17:47:00
Ben 2008-09-29 15:46:00
nameb 2008-12-04 18:24:00
nameb 2008-12-08 09:37:00 (10 個資料列受到影響)