删除具有多个条件的记录

csbfibhn  于 2021-06-21  发布在  Mysql
关注(0)|答案(9)|浏览(265)

我需要排除文件中的重复记录 bidding_price 具有以下条件的列:
表格: bid_account 要检查的列: id =主键 auction_id =每个产品的id bidding_price =插入值(必须检查每个产品的重复性) bid_flag =必须始终等于“d”的值 bidding_type =必须始终等于::“s”的值
它将永远平等地存在于世界上 bidding_price 列中不能有具有相同产品id的相等记录( auction_id ).
不应具有的示例:
拍卖|投标|价格
------10

j13ufse2

j13ufse21#

--0.02
正确答案是:
拍卖|投标|价格
------10

kgsdhlau

kgsdhlau2#

--0.02
我尝试了以下命令:

DELETE ba
    FROM bid_account ba JOIN
         (SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id
          FROM bid_account ba2
          WHERE ba2.bid_flag = 'd' AND ba2.bidding_type = 's'
          GROUP BY ba2.auction_id, ba2.bidding_price
         ) ba2
         ON ba2.auction_id = ba.auction_id AND
            ba2.bidding_price = ba.bidding_price AND
            ba2.max_id < ba.id
WHERE ba.bid_flag = 'd' AND ba.bidding_type = 's' AND ba.auction_id = ba2.auction_id

问题是它删除了多个不应该删除的记录,没有正确地进行验证。我该怎么做?

j2cgzkjk

j2cgzkjk3#

您可以使用主键id删除它。它用于唯一标识要执行删除操作的记录。请参见此处的演示:http://sqlfiddle.com/#!9/603d56/1号
它利用在子查询中选择id(选择它两次将避免错误:不能为更新指定目标表)。子查询类似于您的查询,它选择将保留的id。使用not in意味着删除子查询中与这些id不相等的其余行。

delete e.*
from bid_account e
where e.id not in (
select id from (
    select a.id
    from bid_account a
    join bid_account b
    on a.auction_id=b.auction_id
    and a.bidding_price=b.bidding_price
    and a.bid_flag=b.bid_flag
    and a.bidding_type=b.bidding_type
    where a.bid_flag='d' and a.bidding_type='s'
    and a.id < b.id) tt);
btxsgosb

btxsgosb4#

id是表中的主键,因此可以 MAX(id) 作为您的预订id,然后使用 NOT IN 按id删除而不删除 MAX(id) 你可以试试这个。

DELETE ba FROM bid_account ba
WHERE ba.id NOT IN
(
  SELECT max_id FROM 
    (
      SELECT auction_id, bidding_price, MAX(id) max_id
      FROM bid_account 
      WHERE bid_flag = 'd' AND bidding_type = 's'
      GROUP BY auction_id, bidding_price
    ) t
)

sqlfiddle:http://sqlfiddle.com/#!9月2日5月1日
编辑
如果你想得到最低的id值,你可以使用 MIN(id) 在where子句的子查询中

DELETE ba FROM bid_account ba
WHERE ba.id NOT IN
(
  SELECT min_id FROM 
    (
      SELECT auction_id, bidding_price, MIN(id) min_id
      FROM bid_account 
      WHERE bid_flag = 'd' AND bidding_type = 's'
      GROUP BY auction_id, bidding_price
    ) t
)

sqlfiddle:http://sqlfiddle.com/#!1992年9月1日

aydmsdu9

aydmsdu97#

下面的陈述应该给你所有你需要的记录。

SELECT ba2.auction_id, ba2.bidding_price, MAX(ba2.id) as max_id
FROM bid_account ba2
WHERE ba2.bid_flag = 'd' AND ba2.bidding_type = 's'
GROUP BY ba2.auction_id, ba2.bidding_price;

not in子句应该提供您不需要的所有记录。因此,从bid_帐户中删除id不在(#子查询以获取所需的id#),ba.bid_flag='d'和ba.bidding_type='s';应该删除重复记录。

相关问题