62,046
社区成员
发帖
与我相关
我的任务
分享
create table tb(check_id int, [user_id] int, check_date datetime)
insert into tb select 1 , 1, '2009-09-02'
union all select 2, 1, '2009-09-03'
union all select 3, 2 , '2009-09-02'
--如果同一个user_id 对应日期唯一
select * from tb a where
not exists (
select 1 from tb where [user_id]=a.[user_id] and check_date<a.check_date)
--如果check_id
select * from tb a where
not exists (
select 1 from tb where [user_id]=a.[user_id] and check_id<a.check_id)
/*
check_id user_id check_date
----------- ----------- -----------------------
1 1 2009-09-02 00:00:00.000
3 2 2009-09-02 00:00:00.000
(2 行受影响)
*/
drop table tb
--最后建议字段不要使用user_id,因为他是sql关键字
select * from @t_chk_record a,@t_chk_user b
where time=(select max(time) from @t_chk_record where user_id=a.user_id)
and a.user_id=b.user_id