mysql - 检索拍卖中每个项目的前 2 个出价

Question

我在合并多个表中的数据时遇到问题。我尝试过联接和子查询但无济于事。我基本上需要将 2 个查询合并为一个。我的表格(简化):

库存:

id              int(9)      PrimaryIndex    
lot_number      int(4)
description     text     
reserve         int(9)
current_bid     int(9)
current_bidder  int(6)

成员:

member_id   int(11)         PrimaryIndex
name        varchar(255)

出价:

id          int(9)
lot_id      int(9)
bidder_id   int(5)
max_bid     int(9) 
time_of_bid datetime 

我目前正在使用 2 个单独的查询，其中包含 1000 个批处理，这使得效率非常低。第一个查询:

SELECT S.id, S.lot_number, S.description, S.reserve FROM stock S ORDER BY 
S.lot_number ASC

while 循环内的第二个查询然后获取出价信息:

SELECT DISTINCT B.bidder_id, B.lot_id, B.max_bid, B.time_of_bid,
M.fname, M.lname FROM bids B, members M WHERE B.lot_id=? AND
B.bidder_id=M.member_id ORDER BY B.max_bid DESC LIMIT 2

如果可能的话，下面是我想要的单个查询的输出:

Lot No. | Reserve | Current Bid | 1st Max Bid | 1st Bidder | 2nd Max Bid | 2nd Max Bidder

1       | $100    | $120        | $150        | Steve      | $110        | John
2       | $500    | $650        | $900        | Tom        | $600        | Paul

我仅获得了 MAX(B.bid) 及其相关详细信息 (WHERE S.id=B.id)，但取得了部分成功，但我无法获得每批处理的前 2 个出价。

Answer 1

首先将行号 rn 分配给表 bids 中每组 lot_id 中的行(最高出价获得 1，第二高出价获得2 等等)。最高出价和第二高出价将位于 LEFT JOIN 之后的两个不同行。使用 GROUP BY 将两行合并为一行。

select  s.lot_number, s.reserve, s.current_bid, 
max( case when rn = 1 then b.max_bid end) as first_max_bid,
max( case when rn = 1 then m.name end) as first_bidder,
max( case when rn = 2 then b.max_bid end) as second_max_bid,
max( case when rn = 2 then m.name end ) as second_bidder
from 
stock s
left join
(select * from
  (select *,
   (@rn := if(@lot_id = lot_id, @rn+1,
              if( @lot_id := lot_id, 1, 1))) as rn
   from bids cross join
   (select @rn := 0, @lot_id := -1) param
   order by lot_id, max_bid desc
) t 
where rn <= 2) b
on s.lot_number = b.lot_id
left join members m 
on b.bidder_id = m.member_id
group by s.lot_number, s.reserve, s.current_bid
order by s.lot_number