有一张表(T1)规律如下:
COL1 COL2
1 'A'
1 'B'
2 'C'
2 'C'
2 'D'
2 'D'
3 'E'
3 'E'
3 'E'
. .
. .
. .
现在想把表T1中COL1相等、COL2也相等的记录delete了,这样的delete语句怎么写???
删除完后得到的表如下:COL1 COL2
1 'A'
1 'B'
2 'C'
2 'D'
3 'E'
. .
. .
. .
COL1 COL2
1 'A'
1 'B'
2 'C'
2 'C'
2 'D'
2 'D'
3 'E'
3 'E'
3 'E'
. .
. .
. .
现在想把表T1中COL1相等、COL2也相等的记录delete了,这样的delete语句怎么写???
删除完后得到的表如下:COL1 COL2
1 'A'
1 'B'
2 'C'
2 'D'
3 'E'
. .
. .
. .
select max(rowid) from t1 b
where b.col1 = a.col1
and b.col2 = a.col2
)
可以利用这样的命令来删除表内重复记录: delete from table_name a where rowid< (select max(rowid) from table_name where column1=a.column1 and column2=a.column2 and colum3=a.colum3 and ...)
不过,当表比较大(例如50万条以上)时,这个方法的效率之差令人无法忍受
(select * from t1 where t1.col1 !=
(select max(col1)
from t1 a
where a.col1 = a.col1 and a.col2 = a.col2))
(它没分是不是重复的rows,一律删第一笔)
(select distinct a.col1,a.col2 from t1 a)
where rowid != (select max(rowid) from zzb_userposthis
where userid=a.userid and postid=a.postid )