取拍卖纪录中出价最高的人

隔壁部门这次的拍卖系统故障了，但是拍卖的数据结果还在，友情赞助出个表，获取拍卖纪录中出价最高的人

数据库是SQL SERVER2005

1.拍卖物品表  TRN_Auction

2.拍卖出价纪录表 TRN_Auction_p

出明细结果的SQL

select a.id,a.category,a.info,a.bid,a.first_p,a.Curr_p,a.high_Person,a.item_id,b.name,b.price,b.time from
dbo.TRN_Auction a, dbo.TRN_Auction_p b
where a.id = b.id order by a.id asc,price desc

 

取拍卖纪录中出价最高的人的SQL

(其中黄色标记是本次SQL迷糊了一次的地方)

select m.price,m.name ,a.* from (select b.id,b.name,b.price from dbo.TRN_Auction_p b
where b.price = (
select max(c.price) from
dbo.TRN_Auction_p c
where c.id=b.id group by c.id )
) as m
,
dbo.TRN_Auction a
where a.id=m.id
order by a.id