我有一张表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 行)