删除数据库里的重复数据--sql语句
需要得到以下结果:
下面的语句可以达到要求:
select * from 表 where id in(
SELECT MAX(id) AS id FROM 表 GROUP BY rows) --重复行只查询一条
例如:
id name value
1 a pp
2 a pp
3 b iii
4 b pp
5 b pp
6 c pp
7 c pp
8 c iii
id是主键
要求得到这样的结果
id name value
1 a pp
3 b iii
4 b pp
6 c pp
8 c iii
方法1
delete YourTable
where [id] not in (
select max([id]) from YourTable
group by (name + value))
例子:
id name value
1 a pp
2 a pp
3 b iii
4 b pp
5 b pp
6 c pp
7 c pp
8 c iii
id是主键
要求得到这样的结果
id name value
1 a pp
3 b iii
4 b pp
6 c pp
8 c iii
方法:
delete YourTable
where [id] not in (
select max([id]) from YourTable
group by (name + value))