删除完全相同的记录
如果没有递增字段可以采用临时表技术
SELECT DISTINCT * INTO #tempTable FROM sourceTable
DELETE FROM sourceTable
INSERT INTO sourceTable SELECT * FROM #tempTable
DROP TABLE #tempTable
如果删除的表中存在自动增长列
如:
pid pname
2,"b"
6,"c"
14,"d"
15,"h"
16,"k"
17,"e"
18,"a"
19,"c"
20,"k"
21,"b"
可以采用如下SQL语句
DELETE FROM PInfo
WHERE (pid NOT IN
(SELECT MAX(pid)
FROM pinfo
GROUP BY pname))
如果没有递增字段可以采用临时表技术
SELECT DISTINCT * INTO #tempTable FROM sourceTable
DELETE FROM sourceTable
INSERT INTO sourceTable SELECT * FROM #tempTable
DROP TABLE #tempTable
如果删除的表中存在自动增长列
如:
pid pname
2,"b"
6,"c"
14,"d"
15,"h"
16,"k"
17,"e"
18,"a"
19,"c"
20,"k"
21,"b"
可以采用如下SQL语句
DELETE FROM PInfo
WHERE (pid NOT IN
(SELECT MAX(pid)
FROM pinfo
GROUP BY pname))