求一组数中,最大值方法

--原贴 http://community.csdn.net/Expert/topic/4169/4169825.xml?temp=.4744684

我有一张表A(BMMC,BMBH,LXR,TEL,VALUE)
现在要求按BMBH分类,并把相应的VALUE最大的那条记录显示出来,
即
A
BMMC BMBH,LXR,TEL,VALUE
AA     1   W1  t1  3
BB     2   W2  t2  4
CC     3   W3  t3  5
AA     1   W4  t4  6
AA     1   W5  t5  7
BB     2   W6  t6  8
CC     3   W7  t7  9
显示为
BMMC BMBH,LXR,TEL,VALUE
AA    1    W5  t5   7
BB    2   W6  t6  8
CC    3   W7  t7  9

--方法一:

--测试环境
declare @T table(BMMC varchar(2),BMBH varchar(1),LXR varchar(2),TEL varchar(2),value int)
insert into @t select 'AA','1','W1','t1',3
union all select 'BB','2','W2','t2',4
union all select 'CC','3','W3','t3',5
union all select 'AA','1','W4','t4',6
union all select 'AA','1','W5','t5',7
union all select 'BB','2','W6','t6',8
union all select 'CC','3','W7','t7',9
--查询
select BMMC=(select max(BMMC) from @T where BMBH=a.BMBH),
       BMBH=(select max(BMBH) from @T where BMBH=a.BMBH),
       LXR=(select max(LXR) from @T where BMBH=a.BMBH),
       TEL=(select max(TEL) from @T where BMBH=a.BMBH),
      value=(select max(value) from @T where BMBH=a.BMBH)
from @T a
group by BMBH
--结果
BMMC BMBH LXR  TEL  value      
---- ---- ---- ---- -----------
AA   1    W5   t5   7
BB   2    W6   t6   8
CC   3    W7   t7   9

（所影响的行数为 3 行）

--方法二

declare @T table(BMMC varchar(2),BMBH varchar(1),LXR varchar(2),TEL varchar(2),value int)
insert into @t select 'AA','1','W1','t1',3
union all select 'BB','2','W2','t2',4
union all select 'CC','3','W3','t3',5
union all select 'AA','1','W4','t4',6
union all select 'AA','1','W5','t5',7
union all select 'BB','2','W6','t6',8
union all select 'CC','3','W7','t7',9

select * from @T b where not exists(Select 1 from @T a where a.bmmc=b.bmmc and a.value>b.value )

--结果
BMMC BMBH LXR  TEL  value      
---- ---- ---- ---- -----------
AA   1    W5   t5   7
BB   2    W6   t6   8
CC   3    W7   t7   9

（所影响的行数为 3 行）

原文转自：http://www.ltesting.net