获取每个组的多个 GROUP BY 结果，或使用单独的串联表

Question

我正在开发一个拍卖网络应用程序。现在我有一个包含出价的表，我想从该表中选择每次拍卖的最后 10 个出价。

现在我知道我可以通过使用以下内容来获取最后的出价：

SELECT bids.id FROM bids WHERE * GROUP BY bids.id ORDER BY bids.created

现在我已经读到为 GROUP 设置金额BY结果并不是一件容易的事，实际上我没有找到简单的解决方案，如果有的话我想听听。

但我已经想出了一些解决方案来解决这个问题，但我不确定我是否做得很好。

替代方案

第一件事是创建一个新表，命名为 bids_history。在此表中，我存储最后一项的字符串。

示例：

bids_history
================================================================
auction_id   bid_id     bidders               times
    1        20,25,40   user1,user2,user1     time1,time2,time3

我也必须存储名称和时间，因为我没有找到简单的方法来获取 bid_id(20,25,40) 中使用的字符串，并在连接中使用它。 这样我就可以加入拍卖ID，并且我可以获得最新的结果。

现在，当放置新的出价时，步骤如下：

• 将出价插入出价 获取最后插入的 ID
• 获取此出价的 bids_history 字符串 拍卖品
  • 分解字符串
  • 插入新值
  • 检查是否超过 3 个
  • 内爆数组，然后再次插入字符串

这在我看来不是一个很好的解决方案。 我真的不知道该走哪条路。请记住，这是一个有大量出价的网站，每个拍卖项目最多可以有 15.000 次出价。也许因为这个数量，分组和排序不是一个好方法。如果我错了，请纠正我。

拍卖结束后，我会清理出价表，删除所有出价，并将它们存储在单独的表中。

有人可以帮我解决这个问题吗！

如果您去过，感谢您的阅读..

编辑

我使用的表格是：

bids
======================
id      (prim_key)
aid     (auction id)
uid     (user id)
cbid    (current bid)
created (time created)
======================

auction_products
====================
id        (prim_key)
pid       (product id)
closetime (time the auction closses)

我想要的查询结果：

result
===============================================
auction_products.id   bids.uid   bids.created
2                        6         time1
2                        8         time2
2                        10        time3
5                        3         time1
5                        4         time2
5                        9         time3
7                        3         time1
7                        2         time2
7                        1         time3

所以这是每次拍卖的最新出价，按数字选择，3 或 10

Answer 1

使用用户变量和控制流，我最终得到了这个（只需将<=3替换为<=10 如果您想要十次拍卖）：

SELECT a.*
FROM
 (SELECT aid, uid, created FROM bids ORDER BY aid, created DESC) a,
 (SELECT @prev:=-1, @count:=1) b
WHERE
 CASE WHEN @prev<>a.aid THEN
   CASE WHEN @prev:=a.aid THEN
    @count:=1
   END
 ELSE
   @count:=@count+1
 END <= 3

Answer 2

为什么要在一个查询中执行此操作？

$sql = "SELECT id FROM auctions ORDER BY created DESC LIMIT 10";
$auctions = array();

while($row = mysql_fetch_assoc(mysql_query($sql)))
  $auctions[] = $row['id'];

$auctions = implode(', ', $auctions);
$sql = "SELECT id FROM bids WHERE auction_id IN ($auctions) ORDER BY created LIMIT 10";
// ...

显然，您应该处理例如 $auctions 为空的情况，但我认为这应该可行。

Answer 3

编辑：这是错误的:-)

您将需要使用子查询：

SELECT bids1.id
FROM ( SELECT *
       FROM bids AS bids1 LEFT JOIN
            bids AS bids2 ON bids1.created < bids2.created
                          AND bids1.AuctionId = bids2.AuctionId
       WHERE bid2.id IS NULL)
ORDER BY bids.created DESC
LIMIT 10

因此子查询执行从出价到自身的左连接，将每条记录与具有相同拍卖 ID 和创建日期晚于其自己创建之后的所有记录配对日期。对于最新的记录，不会有其他记录具有更大的创建日期，因此该记录不会包含在联接中，但由于我们使用左联接，因此它将包含在内，所有 bids2 字段均为空，因此是 WHERE bid2.id IS NULL 语句。

因此，子查询每次拍卖只有一行，包含最近出价的数据。然后只需使用 orderby 和 limit 选择前十名即可。

如果您的数据库引擎不支持子查询，您也可以使用视图。

Answer 4

好的，这个应该可以工作：

SELECT bids1.id
FROM bids AS bids1 LEFT JOIN
     bids AS bids2 ON bids1.created < bids2.created
                   AND bids1.AuctionId = bids2.AuctionId
GROUP BY bids1.auctionId, bids1.created
HAVING COUNT(bids2.created) < 9

所以，像以前一样，将出价与其自身进行左连接，这样我们就可以将每个出价与所有其他出价进行比较。然后，首先按拍卖（我们希望每次拍卖的最后十个出价）对其进行分组，然后按创建进行分组。因为左连接将每个出价与所有先前的出价配对，所以我们可以计算每个组的 bids2.created 数量，这将为我们提供该出价之前发生的出价数量。如果该计数< 9（因为第一个的计数 == 0，它的索引为零）它是最近十个出价之一，我们要选择它。

Answer 5

要选择给定拍卖的最后 10 出价，只需创建一个标准化的 bids 表（每个出价 1 条记录）并发出以下查询：

SELECT  bids.id
FROM    bids
WHERE   auction = ?
ORDER BY
        bids.created DESC
LIMIT 10

要选择最后 10 出价对于多次拍卖的 code> bids，请使用以下方法：

SELECT  bo.*
FROM    (
        SELECT  a.id,
                COALESCE(
                (
                SELECT  bi.created
                FROM    bids bi
                WHERE   bi.auction = a.id
                ORDER BY
                        bi.auction DESC, bi.created DESC, bi.id DESC
                LIMIT 1 OFFSET 9
                ), '01.01.1900'
                ) AS mcreated
                COALESCE(
                (
                SELECT  bi.id
                FROM    bids bi
                WHERE   bi.auction = a.id
                ORDER BY
                        bi.auction DESC, bi.created DESC, bi.id DESC
                LIMIT 1 OFFSET 9
                ), 0)
                AS mid
        FROM    auctions a
        ) q
JOIN    bids bo
ON      bo.auction >= q.auction
        AND bo.auction <= q.auction
        AND (bo.created, bo.id) >= (q.mcreated, q.mid)

在 bids (auction、created、id) 上创建一个复合索引，以便快速运行。