-- 方法1
select a.ID,a.Code,a.Qty
from table1 a,
(select Code,
max(Qty) 'Qty'
from table1
group by Code) b
where a.Code=b.Code and a.Qty=b.Qty-- 方法2
select a.*
from table1 a
where not exists
(select 1
from table1 b
where b.Code=a.Code and b.Qty>a.Qty)
调试欢乐多
From table1 a
where exists (select 1 from
( select Code, MAX(Qty)Qty
from table1
group by Code
) b where a.code=b.code and a.qty=b.qty)
select * from table1 as a where qty=(select max(qty) from table1 where code=a.code )
select Max(ID)ID,Code,Max(Qty)Qty from Table1 group by Code
drop table table1
go create table table1
(
Id int identity not null,
Code nvarchar(3),
Qty int
)
insert into table1 select '001',1 union all
select '002',3 union all
select '001',5 union all
select '001',4 union all
select '003',1 union all
select '002',2 union all
select '002',1 union all
select '003',4 union all
select '003',3
--SQL语句
select (select Id from table1 where code=a.code and Qty=a.Qty) as Id ,a.code,a.Qty from (
select code, max(qty) as Qty from table1 group by code
) as a 供参考!