糊涂了,SQL这样的业务处理 应该如何表示

Question

问大家一个问题user                                     accessdate                            access
Freddy Hau                           2008-01-25 14:13:16.370                     0
Jing Dong                            2008-01-11 12:23:08.610                     0
Freddy Hau                           2008-01-23 18:47:34.170                     1
Jing Dong                            2008-01-23 14:57:10.363                     1
michelle                             2008-01-23 14:49:28.977                     1
想变成user                                     accessdate                              access
Freddy Hau                            2008-01-25 14:13:16.370                    0
Jing Dong                             2008-01-23 14:57:10.363                    1
michelle                              2008-01-23 14:49:28.977                    1
汇总相同名字user 记录相同人名的几条记录，
access=0 的记录的 accessdate 比  access=1记录的accessdate 时间大，
就用access=0 记录取代替access=1的记录如果只有一个人名的记录 就保留原记录

Answer 1

select * from [Table] a where not exists(select 1 from [Table] where [user]=a.[user] and accessdate>a.accessdate)

Answer 2

create table #([user] varchar(20),accessdate datetime,access bit)insert into # values('Freddy   Hau','2008-01-25   14:13:16.370',0)
insert into # values('Jing   Dong','2008-01-11   12:23:08.610',0)
insert into # values('Freddy   Hau','2008-01-23   18:47:34.170',1)
insert into # values('Jing   Dong','2008-01-23   14:57:10.363',1)
insert into # values('michelle','2008-01-23   14:49:28.977',1)select * from # a where not exists (select 1 from # where [user]=a.[user] and accessdate>a.accessdate)user                 accessdate              access
-------------------- ----------------------- ------
Freddy   Hau         2008-01-25 14:13:16.370 0
Jing   Dong          2008-01-23 14:57:10.363 1
michelle             2008-01-23 14:49:28.977 1(3 行受影响)

Answer 3

select * from 表名 a where not exists(select 1 from 表名 where user=a.user and accessdate>a.accessdate)
--或者
select * from 表名 a where 
(select count(distinct accessdate)  from 表名 where user=a.user and accessdate>=a.accessdate)=1