检查mysql中的行顺序

Need some help with mysql.

需要一些mysql的帮助。

I have a table full of bids, i need to check if there are more than 3 bids from the same user that are one after another.

我有一个完整的出价表,我需要检查来自同一个用户的出价是否超过3个。

Here is some code for example:

以下是一些代码示例:

$all = sql_get('select all bids from bids where auction_id = 1 order by amount asc');
$total = 0;

foreach ($all as $bid) {
  if ($bid->user_id == $user->id) {
    if (++$total <= 3) continue;
    $bid->burned = true;
    sql_store($bid);
    show_error('you cant have more than 4 bids one after another, the last bid was burned.');
  } else {
    $total = 0;
  }
}

Is there a way to do that in a single query ?

有没有办法在单个查询中执行此操作?

2 个解决方案

#1

SELECT user_id, CASE WHEN @user_id = user_id 
    THEN @rownum := @rownum + 1 
    ELSE ((@user_id := user_id) * 0) + (@rownum := 1) END AS rownum
FROM (  SELECT user_id
        FROM bids, (SELECT @rownum := 0, @user_id := NULL) AS vars
        WHERE auction_id = 1
        ORDER BY created DESC) AS h
HAVING user_id = '{$user->id}' AND rownum >= 3

Simply parse user_id into query and you will know if there are 3 in a row. This asumes that you save the time the rows were created with the created column.

只需将user_id解析为查询,您就会知道连续是否有3个。这假设您使用创建的列保存创建行的时间。

2 个解决方案

#1

更多相关文章

随机推荐