--测试环境 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 行)
如果说 最大记录都在一条记录中 --zlp321002(职业-->烧人民币)的方法好
select a.BMMC,a.BMBH,a.LXR,a.TEL,a.VALUE from A,(select bmmc,max(value) value from table1 group by bmmc) b where a.bmmc=b.mmc and b.value=a.value
select * from A where not exists(Select 1 from A b where a.bmmc=b.bmmc and b.value>a.value )
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',9select * from @T t where not exists (select 1 from @T where BMMC=t.BMMC and BMBH=t.BMBH and value>t.value)--结果 /* BMMC BMBH LXR TEL value ---- ---- ---- ---- ----------- AA 1 W5 t5 7 BB 2 W6 t6 8 CC 3 W7 t7 9(3 row(s) affected) */
select * from A where not exists(Select 1 from A b where a.bmmc=b.bmmc and b.value>a.value )
select distinct * from income where amount like (select max(amount) as Value from income group by userid)
select distinct * from income where amount like (select max(amount) as Value from income group by userid)like 改为 inselect distinct * from income where amount in (select max(amount) as Value from income group by userid)
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 行)
--zlp321002(职业-->烧人民币)的方法好
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',9select * from @T t
where not exists
(select 1 from @T where BMMC=t.BMMC and BMBH=t.BMBH and value>t.value)--结果
/*
BMMC BMBH LXR TEL value
---- ---- ---- ---- -----------
AA 1 W5 t5 7
BB 2 W6 t6 8
CC 3 W7 t7 9(3 row(s) affected)
*/