【SQL】2条完全一样的数据删除一条保留一条
SQL Server 2005 Express 下测试通过, 仅供参考-- 任意的测试表CREATE TABLE test_delete( name varchar(10), value INT);go-- 张三100 与 王五80 是有重复的INSERT INTO test_deleteSELECT '张三', 100UNION ALL SELECT '张三', 100UNION ALL SELECT '李四', 80UNION ALL SELECT '王五', 80UNION ALL SELECT '王五', 80UNION ALL SELECT '赵六', 90UNION ALL SELECT '赵六', 70go-- 测试检索数据SELECT ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no, name, valueFROM test_deleteno name value-------------------- ---------- ----------- 1 李四 80 1 王五 80 2 王五 80 1 张三 100 2 张三 100 1 赵六 70 1 赵六 90--创建视图CREATE VIEW tmp_view ASSELECT ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no, name, valueFROM test_delete--删除数据1> DELETE FROM tmp_view WHERE no != 12> go(2 行受影响)-- 核对结果1>2> select * from test_delete;3> goname value---------- -----------张三 100李四 80王五 80赵六 90赵六 70(5 行受影响)
