cid pid price id
------------------------------
2 5 .0000 17
2 5 .0000 18
2 5 60.0000 33
2 6 80.0000 34
2 5 .0000 20
2 5 90.0000 21
3 6 .0000 22结果
----------------------------------
2 5 33 60.0000
2 6 34 80.0000
3 6 22 .0000 注:需最优SQL语句,谢谢
------------------------------
2 5 .0000 17
2 5 .0000 18
2 5 60.0000 33
2 6 80.0000 34
2 5 .0000 20
2 5 90.0000 21
3 6 .0000 22结果
----------------------------------
2 5 33 60.0000
2 6 34 80.0000
3 6 22 .0000 注:需最优SQL语句,谢谢
insert into #t select 2,5,0,17
union all select 2,5,0,18
union all select 2,5,60,33
union all select 2,6,80,34
union all select 2,5,0,20
union all select 2,5,90,21
union all select 3,6,0,22
select price from #t where id in (select max(id) from #t group by cid,pid)
drop table #t
where not exists( select * from tb where cid=a.cid and pid = c.pid and price>a.price)
--or:select * from tb a
inner join(select cid,pid,max(price) as price from tb group by cid,pid)b
on a.cid=b.cid and a.pid=b.pid and a.price = b.price