--用户表
userid username serial sex
1001 zhangsan abcdjhg M
1002 lisi sdfdddd F
1003 wangwu stfffff F
--考勤表
id userid ktime kcode FT
1 1001 2011-12-09 10:20:00.000 01201001 0001
2 1002 2011-12-09 10:20:00.000 01201002 0001
3 1002 2011-12-10 08:23:00.000 01201002 0002
4 1003 2011-12-08 08:25:00.000 01201003 0021
--查出结果
userid username serial SEX ktime kcode FT
1001 zhangsan abcdjhg M 2011-12-09 10:20:00.000 01201001 0001
1002 lisi sdfdddd F 2011-12-10 08:23:00.000 01201002 0002
1003 wangwu stfffff F 2011-12-08 08:25:00.000 01201003 0021 也就是找出用户表中每个用户最近的那条考勤记录,然后合并成一个新的用户表
userid username serial sex
1001 zhangsan abcdjhg M
1002 lisi sdfdddd F
1003 wangwu stfffff F
--考勤表
id userid ktime kcode FT
1 1001 2011-12-09 10:20:00.000 01201001 0001
2 1002 2011-12-09 10:20:00.000 01201002 0001
3 1002 2011-12-10 08:23:00.000 01201002 0002
4 1003 2011-12-08 08:25:00.000 01201003 0021
--查出结果
userid username serial SEX ktime kcode FT
1001 zhangsan abcdjhg M 2011-12-09 10:20:00.000 01201001 0001
1002 lisi sdfdddd F 2011-12-10 08:23:00.000 01201002 0002
1003 wangwu stfffff F 2011-12-08 08:25:00.000 01201003 0021 也就是找出用户表中每个用户最近的那条考勤记录,然后合并成一个新的用户表
insert into 用户表 select 1001,'zhangsan','abcdjhg','M'
insert into 用户表 select 1002,'lisi','sdfdddd','F'
insert into 用户表 select 1003,'wangwu','stfffff','F'
create table 考勤表(id int,userid int,ktime datetime,kcode varchar(10),FT varchar(10))
insert into 考勤表 select 1,1001,'2011-12-09 10:20:00.000','01201001','0001'
insert into 考勤表 select 2,1002,'2011-12-09 10:20:00.000','01201002','0001'
insert into 考勤表 select 3,1002,'2011-12-10 08:23:00.000','01201002','0002'
insert into 考勤表 select 4,1003,'2011-12-08 08:25:00.000','01201003','0021'
go
select a.userid,a.username,a.serial,a.sex,b.ktime,b.kcode,b.ft
from 用户表 a inner join 考勤表 b on a.userid=b.userid
where not exists(select 1 from 考勤表 where userid=b.userid and ktime>b.ktime)
/*
userid username serial sex ktime kcode ft
----------- ---------- ---------- ---------- ----------------------- ---------- ----------
1001 zhangsan abcdjhg M 2011-12-09 10:20:00.000 01201001 0001
1002 lisi sdfdddd F 2011-12-10 08:23:00.000 01201002 0002
1003 wangwu stfffff F 2011-12-08 08:25:00.000 01201003 0021(3 行受影响)*/
go
drop table 用户表,考勤表
a.userid,a.username,a.serial,a.sex,b.ktime,b.kcode,b.ft
from
用户表 a ,考勤表 b
where
a.userid=b.userid
and
ktime=(select min(ktime) from 考勤表 where userid=b.userid )
a.userid,a.username,a.serial,a.sex,b.ktime,b.kcode,b.ft
from
用户表 a ,考勤表 b
where
a.userid=b.userid
and
ktime=(select max(ktime) from 考勤表 where userid=b.userid )
left join 考勤表 b on a.userid=b.userid and
ktime=(select max(ktime) from 考勤表 where userid=b.userid )
left join 考勤表 b on a.userid=b.userid
where ktime=(select max(ktime) from 考勤表 where userid=b.userid )