试试看
select id, min(amount) as amount
from tablename a, (select id, min(amount) as amount from tablename group by id) b
where a.id = b.id and a.amount > b.amount
group by id
select id, min(amount) as amount
from tablename a, (select id, min(amount) as amount from tablename group by id) b
where a.id = b.id and a.amount > b.amount
group by id
from t
where (select count(*) from t where id=a.id and amount<=a.amount)=2
from tablename a, (select id, min(amount) as amount from tablename group by id) b
where a.id = b.id and a.amount > b.amount
group by a.id
from t
where (select count(*) from t where id=a.id and amount<=a.amount)=n --n>=1
insert tt values (1, 10)
insert tt values (1, 12)
insert tt values (2 , 2332)
insert tt values (2 , 2)
insert tt values (2 , 2323)
insert tt values (2 , 1)
insert tt values (3 , 0)
select * from tt
DECLARE a SCROLL CURSOR FOR
SELECT * FROM tt
declare @a int,@b int,@c int,@d int,@e int
select @e=count(*) from (select id from tt group by id) as a
create table #a(id int,amount int)
OPEN aFETCH ABSOLUTE 2 FROM a into @a,@b
insert #a values (@a,@b)
set @d=1
while @d<(@e-1)
beginselect @c=count(*) from tt where id=@d
FEtch RELATIVE @c from a into @a,@b
insert #a values (@a,@b)
set @d=@d+1
end
close a
DEALLOCATE a
select * from #a