declare @t1 table (e1 varchar(2),e2 varchar(2),e3 varchar(10))insert @t1 select 'A','a','2005-1-1' union all select 'A','b','2005-2-1' union all select 'B','a','2005-9-1' union all select 'B','b','2005-4-1' union all select 'A','a','2005-5-1' union all select 'B','a','2005-2-5' union all select 'B','b','2005-8-1'select * from @t1 where e3 in (select max(e3) from @t1 group by e1)e1 e2 e3 ---- ---- ---------- B a 2005-9-1 A a 2005-5-1(所影响的行数为 2 行)
select * from t1 where e3 in (select Max(e3) from t1 group by e1)
SELECT * from 表 A where not exists (select 1 from 表 where e1=A.e1 and e3>A.e3) order by e1
select a.e1, a.e2, b.max_e3 from t1 a inner join ( select e1,max(e3) as max_e3 from t1 group by e1 ) where a.e1 = b.e1 and a.e3 = b.max_e3
(e1 varchar(2),e2 varchar(2),e3 varchar(10))insert @t1
select 'A','a','2005-1-1' union all
select 'A','b','2005-2-1' union all
select 'B','a','2005-9-1' union all
select 'B','b','2005-4-1' union all
select 'A','a','2005-5-1' union all
select 'B','a','2005-2-5' union all
select 'B','b','2005-8-1'select * from @t1 where e3 in (select max(e3) from @t1 group by e1)e1 e2 e3
---- ---- ----------
B a 2005-9-1
A a 2005-5-1(所影响的行数为 2 行)
*
from
表 A
where not exists
(select 1 from 表 where e1=A.e1 and e3>A.e3)
order by
e1
inner join
(
select e1,max(e3) as max_e3 from t1 group by e1
)
where a.e1 = b.e1 and a.e3 = b.max_e3