基本的查询

[holyten]

存在这么张表
F1 F2 F3
1 A 123
2 B 123
3 A 222
4 A 234
5 B 111

查询的结果希望是以F2为组的，要求F3中最大的数字所在的记录。

3 A 222
2 B 123

[wangkenping]

not exists 用法不理解的话看下面的
http://community.csdn.net/Expert/topic/4399/4399931.xml?temp=.4027521

[ygchboy]

不需要这么麻烦难懂!
这样写就可以了: 假设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
已经检验过了,准确无误!

[lxzm1001]

select * from tablename where f3 in(select max(f3) from tablename group by f2)

[lxzm1001]

select * from tablename where f1 in(select f1 from tablename group by f2 order by f3 desc)

[hglhyy]

会这句说差不多了

select f2,max(f3) f3 from tab group by f2

[ReViSion]

select count(f1),f2,max(f3) f3 from tab group by f2

[lxzm1001]

select t.* from tablename t,(select f2,max(f3) as f3 from tablename group by f2) x where t.f2=x.f2 and t.f3=x.f3

[淡蓝冰]

select f1,f2,max(f3)group by f2 order by f2

[淡蓝冰]

select f1,f2,max(f3) from table1 group by f2 order by f2

[wangkenping]

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 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.f3

drop table #TEMP1


f1 f2 f3
----------- -----------
4 A 234
2 B 123
（所影响的行数为 2 行）

[zouqiang122]

支持一下嘛

[funsuzhou]

select 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

[funsuzhou]

--测试数据：

declare @tb table(F1 int,F2 varchar(10),F3 int)
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',111

select 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 行）
*/

[funsuzhou]

或者


select
a.*
from
@tb a
where
a.f3 = (select max(f3) from @tb where f2=a.f2)

[LandyKing]

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 #TEMP1

SELECT 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.F2

DROP TABLE #TEMP1