CREATE TABLE #TEMP1(
F1 INT
,F2 VARCHAR(255)
,F3 INT
)INSERT INTO #TEMP1
SELECT 1,'A',123
UNION
SELECT 2,'B',123
UNION
SELECT 3,'A',222
UNION
SELECT 4,'A',234
UNION
SELECT 5,'B',111
SELECT * FROM #TEMP1SELECT A.* FROM #TEMP1 A INNER JOIN
(
SELECT F2,MAX(F3) F3
FROM #TEMP1
GROUP BY F2
)B ON A.F3=B.F3 AND A.F2=B.F2DROP TABLE #TEMP1
F1 INT
,F2 VARCHAR(255)
,F3 INT
)INSERT INTO #TEMP1
SELECT 1,'A',123
UNION
SELECT 2,'B',123
UNION
SELECT 3,'A',222
UNION
SELECT 4,'A',234
UNION
SELECT 5,'B',111
SELECT * FROM #TEMP1SELECT A.* FROM #TEMP1 A INNER JOIN
(
SELECT F2,MAX(F3) F3
FROM #TEMP1
GROUP BY F2
)B ON A.F3=B.F3 AND A.F2=B.F2DROP TABLE #TEMP1
F1 INT
,F2 VARCHAR(255)
,F3 INT
)INSERT INTO #TEMP1
SELECT 1,'A',123
UNION
SELECT 2,'B',123
UNION
SELECT 3,'A',222
UNION
SELECT 4,'A',234
UNION
SELECT 5,'B',111
select b.f1,b.f2,b.f3 from #TEMP1 b where not exists (select * from #TEMP1 a where a.f2=b.f2 and b.f3<a.f3)
order by b.f2,b.f3drop table #TEMP1
f1 f2 f3
----------- -----------
4 A 234
2 B 123
(所影响的行数为 2 行)
select f2,f3=max(f3) from @tb group by f2)b
on a.f2=b.f2 and a.f3=b.f3
insert into @tb
select 1,'A',123 union all
select 2,'B',123 union all
select 3,'A',222 union all
select 4,'A',234 union all
select 5,'B',111select a.* from @tb a right join (
select f2,f3=max(f3) from @tb group by f2)b
on a.f2=b.f2 and a.f3=b.f3/*
F1 F2 F3
----------- ---------- -----------
4 A 234
2 B 123(所影响的行数为 2 行)
*/
select
a.*
from
@tb a
where
a.f3 = (select max(f3) from @tb where f2=a.f2)
http://community.csdn.net/Expert/topic/4399/4399931.xml?temp=.4027521
这样写就可以了: 假设t1为table;
select a.*
from t1 a,
(
select f2,max(f3) as f3
from t1
group by f2
) b
where a.f2 = b.f2
and a.f3 = b.f3
已经检验过了,准确无误!