mysql - MYSQL中带条件删除重复记录

Question

我使用的命令在 node.js 上每秒运行一次，它具有排除给定项目的任何重复记录的功能，如示例中所示:AND t1.auction_id = 1335.

DELETE FROM bid_account t1
WHERE t1.id < (Select max(t1.id) FROM bid_account t2 WHERE t1.bidding_price = t2.bidding_price) AND t1.auction_id = 1335;

我需要它来删除在 bidding_price 列中具有相同值的记录，并只保留一个。但重要的是，他不是在整个表格中进行搜索，而是通过 auction_id 列针对我在开头报告的某个项目进行搜索。

我尝试运行上述命令，但它返回以下错误:

#1064 - You have a syntax error in your SQL next to 't1
WHERE t1.id < (Select max(t1.id) FROM bid_account t2 WHERE t1.bidding_price ' na linha 1

这个查询有什么问题？

我使用 MYSQL 数据库，表 bid_account 将 id 列作为索引和主列。

如果我使用下面的 SELECT，它通常会返回口是心非的值。

SELECT bidding_price, count(*) FROM bid_account WHERE `auction_id` = 1335 GROUP BY bidding_price Having Count(*) > 1

Answer 1

MySQL 有两个怪癖。首先，当您为表使用别名时，它需要遵循DELETE。所以，这就是您的意图:

DELETE ba FROM bid_account  ba
WHERE ba.id < (Select max(ba2.id)  -- I assume you want the max from the innter reference
               FROM bid_account ba2
               WHERE ba2.bidding_price = ba.bidding_price
              ) AND
      ba.auction_id = 1335;

但是，这仍然不起作用，因为您在表中引用 bid_account。

所以，我认为你想要更多类似这样的东西:

DELETE ba
    FROM bid_account ba JOIN
         (SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id  -- I assume you want the max from the innter reference
          FROM bid_account ba2
          WHERE ba2.auction_id = 1335
          GROUP BY ba2.auction_id, ba2.bidding_price
         ) ba2
         ON ba2.auction_id = ba.ba2.auction_id AND
            ba2.bidding_price = ba.bidding_price AND
            ba2.max_id > ba.id
WHERE ba.auction_id = 1335;

我觉得这仍然没有用。基于 bidding_price 匹配行似乎不寻常。我希望 auction_id 成为比赛的一部分。我怀疑您想对 bidding_price 和 auction_id 进行 GROUP BY 和 JOIN。