打卡记录表 : CheckCardLogLogID uid CheckTime
1 6 2011-01-01 17:31:55
2 6 2011-01-01 17:31:56
3 2 2011-01-01 17:33:55
4 3 2011-01-01 17:33:55
5 3 2011-01-01 17:35:21
6 3 2011-01-01 17:35:58
7 5 2011-01-01 17:39:50
8 5 2011-01-01 17:39:58下班打卡,可多次,取后一次要求查出每人(uid)下班时间
select uid,max(CheckTime) as 下班时间 from tb group by uid
where not exits (select 1 from tb where B where A.checktime<B.checktime)
1 6 2011-01-01 17:31:55 一代卡
2 6 2011-01-01 17:31:56 一代卡
3 2 2011-01-01 17:33:55 一代卡
4 3 2011-01-01 17:33:55 一代卡
5 3 2011-01-01 17:35:21 一代卡
6 3 2011-01-01 17:35:58 一代卡
7 5 2011-01-01 17:39:50 二代卡
8 5 2011-01-01 17:39:58 二代卡我是要列出表里 所有的资料select uid,max(CheckTime) as 下班时间 from tb group by uid
这个是不行的
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58'
go
select uid,max(checktime) from CheckCardLog group by uid
go
drop table CheckCardLog
/*
uid
----------- -----------------------
2 2011-01-01 17:33:55.000
3 2011-01-01 17:35:58.000
5 2011-01-01 17:39:58.000
6 2011-01-01 17:31:56.000(4 行受影响)*/
select uid,CONVERT(varchar(100),CheckTime, 23),max(CheckTime) as 下班时间
from tb
group by uid,CONVERT(varchar(100),CheckTime, 23):
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56','一代卡'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21','一代卡'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58','一代卡'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50','二代卡'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58','二代卡'
go
select uid,max(checktime),card from CheckCardLog group by uid,card
go
drop table CheckCardLog
/*
uid card
----------- ----------------------- -----
5 2011-01-01 17:39:58.000 二代卡
2 2011-01-01 17:33:55.000 一代卡
3 2011-01-01 17:35:58.000 一代卡
6 2011-01-01 17:31:56.000 一代卡(4 行受影响)*/
create table CheckCardLog(LogID int,uid int,CheckTime datetime,Card nvarchar(5))
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56','一代卡'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21','一代卡'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58','一代卡'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50','二代卡'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58','二代卡'
insert into CheckCardLog select 9,6,'2011-01-02 17:31:55','一代卡'
insert into CheckCardLog select 10,6,'2011-01-02 17:31:56','一代卡'
insert into CheckCardLog select 11,2,'2011-01-02 17:33:55','一代卡'
insert into CheckCardLog select 12,3,'2011-01-02 17:33:55','一代卡'
insert into CheckCardLog select 13,3,'2011-01-02 17:35:21','一代卡'
insert into CheckCardLog select 14,3,'2011-01-02 17:35:58','一代卡'
insert into CheckCardLog select 15,5,'2011-01-02 17:39:50','二代卡'
insert into CheckCardLog select 16,5,'2011-01-02 17:39:58','二代卡'
go
select * from checkcardlog a
where not exists(select 1 from checkcardlog where uid=a.uid and checktime>a.checktime and convert(varchar(10),checktime,120)=convert(varchar(10),a.checktime,120))
go
drop table CheckCardLog
/*
LogID uid CheckTime Card
----------- ----------- ----------------------- -----
2 6 2011-01-01 17:31:56.000 一代卡
3 2 2011-01-01 17:33:55.000 一代卡
6 3 2011-01-01 17:35:58.000 一代卡
8 5 2011-01-01 17:39:58.000 二代卡
10 6 2011-01-02 17:31:56.000 一代卡
11 2 2011-01-02 17:33:55.000 一代卡
14 3 2011-01-02 17:35:58.000 一代卡
16 5 2011-01-02 17:39:58.000 二代卡(8 行受影响)*/
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56','一代卡'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21','一代卡'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58','一代卡'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50','二代卡'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58','二代卡'
go
select * from CheckCardLog A
where not exists (select 1 from CheckCardLog B where A.uid = B.uid and A.checktime < B.checktime)
go
drop table CheckCardLog
LogID Uid GuestID StartDay EndDay CreateDay Re
select uid,max(checktime),card from CheckCardLog group by uid,card
select * from tb a where LogID in
(select top 1 LogID from tb
where Uid=a.Uid and GuestID=a.GuestID order by CreateDay desc)