sql - 如何修复此返回不正确值的 SQL 查询？

Question

我正在编写一个 SQL 查询，它将返回某个用户失去的拍卖列表，例如在 eBay 上。

这是我的 table :

bid_id  bid_belongs_to_auction  bid_from_user   bid_price
6       7                       1               15.00
8       7                       2               19.00
13      7                       1               25.00

有问题的区域是这个(取 self 的完整查询，放在问题的末尾):

      AND EXISTS (
              SELECT 1 
                FROM bids x 
               WHERE x.bid_belongs_to_auction = bids.bid_belongs_to_auction
                 AND x.bid_price > bids.bid_price
                 AND x.bid_from_user <> bids.bid_from_user
                 )

问题是查询返回了所有出价更高的拍卖，但忽略了用户更高的出价。

所以，上面的查询有效的例子:

bid_id  bid_belongs_to_auction  bid_from_user   bid_price
6       7                       1               15.00
7       7                       2               18.00

在这种情况下，用户 1 被退回为输掉了拍卖，因为有另一个出价高于该用户的出价。

但是，这里是查询不起作用的时候:

bid_id  bid_belongs_to_auction  bid_from_user   bid_price
6       7                       1               15.00
8       7                       2               19.00
13      7                       1               25.00

在这种情况下，用户 1 被错误地返回为输掉了拍卖，因为有另一个出价高于他之前的出价，但用户已经给出了更高的出价。

如果它很重要，这是我的完整查询，但我认为没有必要解决上述问题，但我还是将其发布在这里:

$query = "
   SELECT
      `bid_belongs_to_auction`,
      `auction_unixtime_expiration`,
      `auction_belongs_to_hotel`,
      `auction_seo_title`,
      `auction_title`,
      `auction_description_1`
   FROM (
      SELECT
         `bid_belongs_to_auction`,
         `bid_from_user`,
         MAX(`bid_price`) AS `bid_price`,
         `auctions`.`auction_enabled`,
         `auctions`.`auction_unixtime_expiration`,
         `auctions`.`auction_belongs_to_hotel`,
         `auctions`.`auction_seo_title`,
         `auctions`.`auction_title`,
         `auctions`.`auction_description_1`
      FROM `bids`
      LEFT JOIN `auctions` ON `auctions`.`auction_id`=`bids`.`bid_belongs_to_auction`
      WHERE `auction_enabled`='1' AND `auction_unixtime_expiration` > '$time' AND `bid_from_user`='$userId'
      AND EXISTS (
              SELECT 1 
                FROM bids x 
               WHERE x.bid_belongs_to_auction = bids.bid_belongs_to_auction
                 AND x.bid_price > bids.bid_price
                 AND x.bid_from_user <> bids.bid_from_user
                 )
      GROUP BY `bid_belongs_to_auction`
   ) AS X
   WHERE `bid_from_user`='$userId'
";

Answer 1

这是一种不同的方法:

$query = "
   SELECT
      `max_bids`.`bid_belongs_to_auction`,
      `auctions`.`auction_unixtime_expiration`,
      `auctions`.`auction_belongs_to_hotel`,
      `auctions`.`auction_seo_title`,
      `auctions`.`auction_title`,
      `auctions`.`auction_description_1`
   FROM `auctions`
     INNER JOIN (
        SELECT
           `bid_belongs_to_auction`,
           MAX(`bid_price`) AS `auction_max_bid`,
           MAX(CASE `bid_from_user` WHEN '$userId' THEN `bid_price` END) AS `user_max_bid`
        FROM `bids`
        GROUP BY `bid_belongs_to_auction`
     ) AS `max_bids` ON `auctions`.`auction_id` = `max_bids`.`bid_belongs_to_auction`
   WHERE `auctions`.`auction_enabled`='1'
     AND `auctions`.`auction_unixtime_expiration` > '$time'
     AND `max_bids`.`user_max_bid` IS NOT NULL
     AND `max_bids`.`user_max_bid` <> `max_bids`.`auction_max_bid`
";

基本上，当您检索所有拍卖的最高出价时，您也会同时检索特定用户的最高出价。下一步是将获得的列表加入到 auctions 中。表并在用户的最高出价不等于拍卖的最高出价时应用额外的过滤器。

注:`max_bids`.`user_max_bid` IS NOT NULL条件可能是不必要的。在 SQL Server 中肯定会如此，因为 `max_bids`.`user_max_bid` <> `max_bids`.`auction_max_bid` 暗示了非空性。健康)状况。我不确定它在 MySQL 中是否相同。