收藏到QQ书签我有一张表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 行）