问题:数据库表t,根据name和age字段去除重复值,重复值留1条数据,其他的重复值标记为status=0
mysql 5.6版本SQL语句(版本不一样sql可能会有变化)
UPDATE t set status=0 WHERE id in
(select id from (SELECT id FROM t where name in(
SELECT name FROM t group by name,age having COUNT(1)>1)
and id not in (SELECT max(id) FROM t group by name,age having COUNT(1)>1)) as A);
结果如下图: