最近處理的系統(tǒng)中出現(xiàn)了大量重復(fù)數(shù)據(jù)，身份證號和姓名一樣。而身份證號是作為用戶登錄系統(tǒng)的用戶名，這樣如果用戶修改了密碼，就有可能出現(xiàn)登錄不了的情況。
參閱了相關(guān)資料后，找到一種比較方便的刪除重復(fù)數(shù)據(jù)的方法，記錄如下（方便以后參考）：
1
delete from t_test where rowid not in
2(select max(t.rowid) from t_test t group by name,cardid);
備份其它的語句:
根據(jù)身份證號碼更新考生的性別信息：
update t_examinee set eneesex='男' where eneesex='X' and length(cardid)=18 and substr(cardid, 17, 1) in ('1','3','5','7','9')
update t_examinee set eneesex='女' where eneesex='X' and length(cardid)=18 and substr(cardid, 17, 1) in ('0','2','4','6','8')
update t_examinee set eneesex='男' where eneesex='X' and length(cardid)=15 and substr(cardid, 15, 1) in ('1','3','5','7','9')
update t_examinee set eneesex='女' where eneesex='X' and length(cardid)=15 and substr(cardid, 15, 1) in ('0','2','4','6','8')
更新人員的身份證號碼信息：
update t_mytable set cardid=substr(cardid,0,17)||'X' where cardid like '%x' and cardtype='居民身份證' and length(cardid)=18
使用A表的數(shù)據(jù)更新B表的對應(yīng)數(shù)據(jù)：
update t_e1 set (inyear) = (select exyear as inyear
from t_tempyear where t_e1.eneeid=t_tempyear.eneeid)
where exists(select 1 from t_tempyear where t_tempyear.eneeid=t_e1.eneeid)