这是对这里问题的跟进。
假设我在上述问题中提出的模式中有另一列名为“主”,所以现在它看起来如下所示:
石材桌:
stone_id = 412 upcharge_title = "sapphire" primary = 1
stone_id = 412 upcharge_title = "sapphire" primary = 0我想删除"stone_id“和"upcharge_title”字段中重复的行,并在“主”字段上的值等于0,但保留另一行的主值等于1?
我怎样才能用MySQL做到这一点呢?
发布于 2020-04-23 19:46:12
您可以通过自连接来完成这一任务:
delete t1
from tablename t1 inner join tablename t2
on t1.stone_id = t2.stone_id and t1.upcharge_title = t2.upcharge_title
and t1.primary = 0 and t2.primary = 1 见演示。
发布于 2020-04-23 19:47:04
一种方法是:
delete t
from t join
(select stone_id, upcharge_title, max(primary) as max_primary
from t
group by stone_id, upcharge_title
) tt
using (stone_id, upcharge_title)
where t.primary = 0 and tt.max_primary = 1;发布于 2020-04-23 19:49:20
可以使用子查询方法。
delete from tbl
where primary != 0 and (stone_id, upcharge_title) = (
select stone_id, upcharge_title
from tbl
group by stone_id, upcharge_title
having count(*) > 1
)https://stackoverflow.com/questions/61395630
复制相似问题