删除单表中多个字段联合为重复数据的数据,只保留id最大的数据

--方法一:

DELETE 
FROM
	`user` 
WHERE
( NAME, age ) IN ( SELECT name,age FROM (SELECT NAME, age FROM `user` GROUP BY NAME, age HAVING count( * ) > 1) t1 ) 
AND id NOT IN ( SELECT id FROM (SELECT MAX( id ) AS id FROM USER GROUP BY NAME, age HAVING count( * ) > 1) t2 )

--方法二:
DELETE 
FROM
	`user` 
WHERE
id IN (
SELECT id FROM
(SELECT id 
 FROM
	`user` t1 
 WHERE id NOT IN ( SELECT MAX( id ) FROM `user` WHERE NAME = t1.NAME AND age = t1.age ) 
 ) t2 )

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值