这个子查询是不是在group后面把除了pk外的字段都列出来就好了? select min(id) from tb group by col2,col2 having count(*)>1改成:select min(id) from tb group by col2,col3,col4............... having count(*)>1
delete a from tt a inner join tt b on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and a.col4=b.col4 and a.id>b.id
from tb A,(select id from tb where id not in (select min(id) from tb group by col2,col2 having count(*)>1)) B
where A.id =B.id
原表:
id(pk) col1 col2 col3 col4......................
1 value1 value2 value3 value4
2 value2 value2 value3 value4
3 value3 value2 value3 value4
4 value1 value2 value3 value4
希望返回结果:
id(pk) col1 col2 col3 col4......................
1 value1 value2 value3 value4
2 value2 value2 value3 value4
3 value3 value2 value3 value4因为,原表第一条数据和第四条数据除了PK外,其他行的内容都一样所以删除啊~
select min(id) from tb group by col2,col2 having count(*)>1改成:select min(id) from tb group by col2,col3,col4............... having count(*)>1
on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3 and a.col4=b.col4 and a.id>b.id
我db的数据量比较大啊~大概有1kw条啊~所以想效率高点啊~您说的这个我也执行过~运行了大概2个小时还是没有结果啊。
建一个新表得了。