34,588
社区成员
发帖
与我相关
我的任务
分享
--用户表
1001 zhangsan M
1002 lisi F
1003 wangwu M
--考勤表
1111 1001 2011-12-09 10:20:00.000
1112 1002 2011-12-09 10:20:00.000
1113 1002 2011-12-10 08:23:00.000
--查出结果
1001 zhangsan 2011-12-09 10:20:00.000
1002 lisi 2011-12-10 08:23:00.000
1003 wangwu NULL
--rtime 为考勤时间
select u.userid, u.uname, max(r.rtime) last_rtime FROM USERS u
left join records r on u.userid = r.userid
group by u.userid, u.uname
order by u.userid
select id,userid,考勤时间 from 考勤记录表 a where not exists(select 1 from 考勤记录表 where userid=a.userid and 考勤时间>a.考勤时间)
select * from 考勤记录表 a where id =
(select top 1 id from 考勤记录表 b where userid = a.userid order by 考勤时间 desc)
select a.姓名,max(b.time) from a inner join b
on a.userid = b.userid group by a.name
select * from a where a.name in(select a.name,max(b.time) from a inner join b
on a.userid = b.userid group by a.name);
select
a.*,b.考勤时间
from
a left join b
on
a.userid=b.userid
and
not exists(select 1 from b t where userid=b.userid and 考勤时间>b.考勤时间)