select max(ID) ID,TNAME,max(TTIME) TTIME from 表 group by TNAME
Select * from 表 A where exists (select 1 from 表 where TNAME<>A.TNAME and A.TTIME>TTIME) /*ID(主键) TNAME(姓名) TTIME(时间) 2 test 2005-12-23 4 test22 2005-11-25 5 test33 2005-12-25 */
Select * from 表 A where not exists (select 1 from 表 where TNAME=A.TNAME and TTIME>A.TTIME )
create table #t(ID int,TNAME varchar(20),TTIME datetime)insert into #t values(1, 'test', '2005-11-12') insert into #t values(2, 'test', '2005-12-23') insert into #t values(3, 'test22', '2005-11-23') insert into #t values(4, 'test22', '2005-11-25') insert into #t values(5, 'test33', '2005-12-25')Select * from #t A where TTIME in (select max(TTIME) from #t where A.TNAME=TNAME) order by ID
Select * from 表 A where not exists (select 1 from 表 where TNAME=A.TNAME and TTIME>A.TTIME) --or: Select * from 表 A inner join (select tname,max(TTIME) as TTIME from 表 group by tname)B on A.tname=B.tname
from 表 A
where exists
(select 1 from 表 where TNAME<>A.TNAME and A.TTIME>TTIME)
/*ID(主键) TNAME(姓名) TTIME(时间)
2 test 2005-12-23
4 test22 2005-11-25
5 test33 2005-12-25
*/
*
from
表 A
where not exists
(select 1
from
表
where TNAME=A.TNAME and TTIME>A.TTIME
)
insert into #t values(2, 'test', '2005-12-23')
insert into #t values(3, 'test22', '2005-11-23')
insert into #t values(4, 'test22', '2005-11-25')
insert into #t values(5, 'test33', '2005-12-25')Select *
from #t A
where TTIME in (select max(TTIME) from #t where A.TNAME=TNAME)
order by ID
from 表 A
where not exists
(select 1 from 表 where TNAME=A.TNAME and TTIME>A.TTIME)
--or:
Select *
from 表 A
inner join (select tname,max(TTIME) as TTIME from 表 group by tname)B
on A.tname=B.tname