代码之家 › 专栏 › 技术社区 › MattBelanger

MySQL查询:竞价中标

mysql sql

MattBelanger · 技术社区 · 14 年前

我有一个小的竞标系统,我正在使用一个幻想拍卖草案。我尝试使用下面的查询来提取每个玩家的最大出价。但是,它并没有给我最高出价,它只是给我第一个进入数据库的出价。

SELECT Bid.id FROM bids AS Bid 
WHERE Bid.active =1
GROUP BY player_id HAVING MAX( Bid.amount )

以下是投标表布局,以防有帮助:

CREATE TABLE IF NOT EXISTS `bids` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `player_id` int(10) NOT NULL,
  `amount` int(6) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `winning_bid` int(1) NOT NULL DEFAULT '0',
  `active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

5 回复 | 直到 14 年前

Khorkrak 14 年前

select max(amount), player_id from bids 
 where active = 1
 group by player_id
 order by 1 desc;

Donnie 14 年前

ever子句需要进行布尔检查。所以,你的 having MAX(Bid.Amount) 不会像你想象的那样去做。你(理论上)想要的是 having Bid.Amount = MAX(Bid.Amount) 尽管这会导致更多的问题,因为从技术上讲,你应该分组讨论 Bid.Amount 因为它存在于一个集合之外,而这根本不是你要做的。

(警告,下面的查询未经测试,但应该非常接近)

如果您只想对单个玩家进行最大出价,那么您实际上应该做的非常简单,在按出价顺序降序排序时选择第一个记录:

select
  b.id
from
  bids b
where
  b.active = 1
  and b.player_id = @playerID
order by
  b.bids desc
limit 1

如果你想要所有的玩家,这个查询仍然会给你一个起点,你只需要传递给每个人 player_id 对它:

select
  bid.playerID,
  bid.id
from
  bids bid
  join (
    select
      b.id
    from
      bids b
    where
      b.active = 1
    order by
      b.bids desc
    limit 1
  ) maxbid on maxbid.playerid = bid.playerid

VeeArr 14 年前

你可以试试:

SELECT Bid.id
  FROM bids AS Bid,
       (SELECT player_id, MAX(amount) AS player_max
          FROM bids
          WHERE active=1
          GROUP BY player_id) AS max_amounts
  WHERE Bid.player_id = max_amounts.player_id
    AND Bid.amount = max_amounts.amount;

不过,我不确定打领带时的行为。

Wrikken 14 年前

或者,霍克拉克的另一个选择(当然也行):

SELECT Bid.id FROM bids AS Bid 
LEFT JOIN bids as x
ON x.amount > Bid.amount
AND x.active = 1
AND x.player_id = Bid.player_id
WHERE Bid.active = 1 AND x.player_id IS NULL
GROUP BY player_id

sarme 14 年前

这里有一个使用行排名来找到最高出价的。你也可以用类似的方法找到第二高,第三高,等等…


SELECT id, player_id, amount 
FROM 
( 
select b.id, b.player_id, b.amount, count(*) as num 
from bids b left outer join bids b2 
on b.player_id = b2.player_id 
AND b.amount = b2.timestamp 
AND b.active = 1 
group by b.id, b.player_id, b.amount 
)
r 
WHERE
num = 1