1、查找表中多餘的重複記錄,重複記錄是根據單個字段(peopleId)來判斷
select * from peoplewhere peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)
2、刪除表中多餘的重複記錄,重複記錄是根據單個字段(peopleId)來判斷,只留有rowid最小的記錄
delete from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) > 1)and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多餘的重複記錄(多個字段)
select * from vitae awhere (leId,) in (select peopleId,seq from vitae group by peopleId,seq having
擴展資料
FROM子句指定SELECT語句查詢及與查詢相關的表或視圖。在FROM子句中最多可指定256個表或視圖,它們之間用逗號分隔。
在FROM子句同時指定多個表或視圖時,如果選擇列表中存在同名列,這時應使用對象名限定這些列所屬的表或視圖。
例如在usertable和citytable表中同時存在cityid列,在查詢兩個表中的cityid時應使用下面語句格式加以限定:
SELECTusername,id
FROMusertable,citytable
id=id
在FROM子句中可用以下兩種格式為表或視圖指定別名: