查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断 SELECT * FROM people WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING count(peopleId) > 1)
删除表中多余的重复记录,重复记录是根据单个字段(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)
查找表中多余的重复记录(多个字段) SELECT * FROM vitae a WHERE (a.peopleId,a.seq) IN (SELECT peopleId,seq FROM vitae GROUP BY peopleId,seq HAVING count(*) > 1)
删除表中多余的重复记录(多个字段),只留有rowid最小的记录 DELETE FROM vitae a WHERE (a.peopleId,a.seq) IN (SELECT peopleId,seq FROM vitae GROUP BY peopleId,seq HAVING count(*) > 1) AND rowid NOT IN (SELECT min(rowid) FROM vitae GROUP BY peopleId,seq HAVING count(*)>1)
查找表中多余的重复记录(多个字段),不包含rowid最小的记录 SELECT * FROM vitae a WHERE (a.peopleId,a.seq) IN (SELECT peopleId,seq FROM vitae GROUP BY peopleId,seq HAVING count(*) > 1) AND rowid NOT IN (SELECT min(rowid) FROM vitae GROUP BY peopleId,seq HAVING count(*)>1)参考一下吧,如果你的表中没有id(rowid),也可以根据时间来判断保留哪一条(最早的)。
DELETE A FROM [TB] A WHERE EXISTS(SELECT 1 FROM [TB] WHERE CustomerName=A.CustomerName AND CreateTime>A.CreateTime)
select CustomerID,CustomerName,CreateTime ,rownum from ( select CustomerID,CustomerName,CreateTime , ROW_NUMBER() over(partition by CustomerName order by CreateTime) as rownum from t0326 ) as a where a.rownum = 1
SELECT * FROM people
WHERE peopleId IN (SELECT peopleId FROM people GROUP BY peopleId HAVING count(peopleId) > 1)
删除表中多余的重复记录,重复记录是根据单个字段(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)
查找表中多余的重复记录(多个字段)
SELECT * FROM vitae a
WHERE (a.peopleId,a.seq) IN (SELECT peopleId,seq FROM vitae GROUP BY peopleId,seq HAVING count(*) > 1)
删除表中多余的重复记录(多个字段),只留有rowid最小的记录
DELETE FROM vitae a
WHERE (a.peopleId,a.seq) IN (SELECT peopleId,seq FROM vitae GROUP BY peopleId,seq HAVING count(*) > 1)
AND rowid NOT IN (SELECT min(rowid) FROM vitae GROUP BY peopleId,seq HAVING count(*)>1)
查找表中多余的重复记录(多个字段),不包含rowid最小的记录
SELECT * FROM vitae a
WHERE (a.peopleId,a.seq) IN (SELECT peopleId,seq FROM vitae GROUP BY peopleId,seq HAVING count(*) > 1)
AND rowid NOT IN (SELECT min(rowid) FROM vitae GROUP BY peopleId,seq HAVING count(*)>1)参考一下吧,如果你的表中没有id(rowid),也可以根据时间来判断保留哪一条(最早的)。
select CustomerID,CustomerName,CreateTime ,
ROW_NUMBER() over(partition by CustomerName order by CreateTime) as rownum from t0326
) as a
where a.rownum = 1