MySQL 依次选择和分组行

Question

我正在开发一个拍卖应用程序，其中的出价保存在具有以下格式的表格中:

id | user_id | auction_id | placed_at

我想按用户 ID 对出价进行分组并选择它们的计数，但前提是用户 ID 一个接一个地出现。出价按 placed_at 降序排列。这就是我的意思:

1 | 1 | 1 | 01-04-2010 00:00:06
2 | 1 | 1 | 01-04-2010 00:00:05
3 | 2 | 1 | 01-04-2010 00:00:04
4 | 2 | 1 | 01-04-2010 00:00:03
5 | 3 | 1 | 01-04-2010 00:00:02
6 | 2 | 1 | 01-04-2010 00:00:01

在这种情况下，我应该得到:

count | user_id | auction_id
  2   |    1    |     1
  2   |    2    |     1
  1   |    3    |     1
  1   |    2    |     1

我怎样才能达到这个结果？结果将用于生成拍卖的事件流，因此它们将用于生成字符串，例如“John Smith 在此拍卖中出价 2 次”。如果有帮助，该应用程序是使用 Ruby on Rails 构建的。

提前谢谢你。

Answer 1

SELECT  user_id, auction_id, COUNT(*)
FROM    (
        SELECT  @r := @r + CASE WHEN @user_id = user_id AND @auction_id = auction_id THEN 0 ELSE 1 END AS _g,
                user_id, auction_id,
                @user_id := user_id,
                @auction_id := auction_id
        FROM    (
                SELECT  @r := 0,
                        @user_id := NULL,
                        @auction_id := NULL
                ) vars,
                mytable
        ORDER BY
                auction_id DESC, placed_at DESC
        ) q
GROUP BY
        user_id, auction_id, _g
ORDER BY
        _g