打卡程序，求SQL

[三散人]

打卡记录表 ： CheckCardLog

LogID 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

[rucypli]

select * from tb A
where not exits (select 1 from tb where B where A.checktime<B.checktime)

[三散人]

LogID uid CheckTime Card
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
这个是不行的

[-晴天]

create table CheckCardLog(LogID int,uid int,CheckTime datetime)
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): 

[-晴天]

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','二代卡'
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 行受影响)

*/

[三散人]

我要列出表里 所有人下班时间，卡种，记录编号， 怎么弄哦

[Spade_J]

select a.* from  CheckCardLog a,(select uid,max(CheckTime) as CheckTime from CheckCardLog group by uid) x where a.uid=x.uid and a.CheckTime = x.CheckTime

[-晴天]

如果有多个工作日,则:

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 行受影响)

*/

[Q315054403]

要从结构设计解决，比如补卡、班次等。。。非一SQL之力

[Iovswety]

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','二代卡'
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

[O爱咋咋地O]

[Quote=引用 9 楼 qianjin036a 的回复:]
如果有多个工作日,则:

SQL code
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 sel……
[/Quote]
这个好

[三散人]

哭，感谢大家，

如果是这个表呢？该怎么写SQL

这个表是合约表，一个客户可以有多个合约（续约），

我要查询 每个客户有效的合约（取最后一次创建时间）

编号 我方签约人 客户ID 开始日期 结束日期 创建日期 备注
LogID Uid GuestID StartDay EndDay CreateDay Remark

[三散人]

表没设计好，郁闷都要死啊，

[chuanzhang5687]

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)

[三散人]

结贴，给分，谢谢SSP2009