mysql - 如何运行查询以保留最多 3 行同名

Question

因此，如果有超过 3 个相同的产品(同名 - item_name)处于事件状态 price_good，我将尝试运行 SQL 查询以停用产品列表。所以基本上如果有 20 个产品具有相同的名称并且其中 10 个处于事件状态 price_good = 1 查询应该禁用另外 7 个 price_good = 0 以仅保留3 个有效产品。

表的结构:

id     item_name        price_good
1      Row 1            1
2      Row 1            1
3      Row 1            0
4      Row 1            0
5      Row 1            1
6      Row 1            1
7      Row 1            1
8      Row 1            1
9      Row 2            0
10     Row 2            1

我正在尝试运行的查询:

UPDATE ss_remote_inventories t1 
JOIN
  (
    SELECT item_name
      FROM ss_remote_inventories
      WHERE price_good = 1
     GROUP BY item_name
    HAVING COUNT(*) > 3
  ) t2
  ON t1.item_name = t2.item_name
  SET t1.price_good = 0;

Answer 1

您可以使用以下查询来更新您的表以保留同一项目的前 3 行

update ss_remote_inventories t
join (
  select a.*
  from ss_remote_inventories a
  where a.price_good = 1  
  and (
    select count(*)
    from ss_remote_inventories b
    where a.item_name = b.item_name
    and b.price_good = 1
    and a.id > b.id
  ) > 2
) t1 on t.id = t1.id 
set t.price_good = 0

Demo

内部查询计算每一行的排名，如 demo对于使用主键和更新查询的同一项目，它只过滤排名大于 2 的行并使用 id 与主表连接

对于更新版本 5.7 将其修复为 where 子句中的子查询

update ss_remote_inventories t
set t.price_good = 0
where id in (
    select id from(
      select a.*
      from ss_remote_inventories a
      where a.price_good = 1  
      and (
        select count(*)
        from ss_remote_inventories b
        where a.item_name = b.item_name
        and b.price_good = 1
        and a.id > b.id
      ) > 2
    ) t1
);

Demo rextester.com/NQEOT48696