在下面这个表中，id是主键如何得到，如何获取到每个personID最近的一条记录,如在下表中
id personID mytime
1 001 2011-11-15
2 001 2011-11-12
3 002 2011-11-13
4 003 2011-11-16
5 003 2011-11-08
6 003 2011-12-12
要求查询返回结果：
id personID mytime
1 001 2011-11-15
3 002 2011-11-13
6 003 2011-12-12
------解决方案--------------------
- SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (id int,personID nvarchar(6),mytime datetime)insert into [TB]select 1,'001','2011-11-15' union allselect 2,'001','2011-11-12' union allselect 3,'002','2011-11-13' union allselect 4,'003','2011-11-16' union allselect 5,'003','2011-11-08' union allselect 6,'003','2011-12-12'select * from [TB]SELECT *FROM TB AWHERE EXISTS ( SELECT * FROM TB WHERE A.personid = personid AND A.mytime > mytime ) /*id personID mytime1 001 2011-11-15 00:00:00.0004 003 2011-11-16 00:00:00.0006 003 2011-12-12 00:00:00.000*/
------解决方案--------------------
- SQL code
SELECT *FROM TB AWHERE NOT EXISTS ( SELECT * FROM TB WHERE A.personid = personid AND A.mytime < mytime ) /*id personID mytime1 001 2011-11-15 00:00:00.0003 002 2011-11-13 00:00:00.0006 003 2011-12-12 00:00:00.000*/
------解决方案--------------------
- SQL code
select * from tb t where mytime=(select max(mytime) from tb where personid =t.personid)
------解决方案--------------------
- SQL code
if object_id('tb','U') is not null drop table tbgocreate table tb( id int identity(1,1), personID varchar(10), mytime varchar(10))goinsert into tb (personID,mytime)select '001','2011-11-15' union allselect '001','2011-11-12' union allselect '002','2011-11-13' union allselect '003','2011-11-16' union allselect '003','2011-11-08' union allselect '003','2011-11-12'goselect * from tb a where not exists(select 1 from tb where personID=a.personID and mytime>a.mytime)/*id personID mytime----------- ---------- ----------1 001 2011-11-153 002 2011-11-134 003 2011-11-16(3 行受影响)*/