CREATE TABLE test_delete( name varchar(10), value INT ); go -- 張三100 與 王五80 是有重復(fù)的 INSERT INTO test_delete SELECT '張三', 100 UNION ALL SELECT '張三', 100 UNION ALL SELECT '李四', 80 UNION ALL SELECT '王五', 80 UNION ALL SELECT '王五', 80 UNION ALL SELECT '趙六', 90 UNION ALL SELECT '趙六', 70 go -- 測試檢索數(shù)據(jù) SELECT ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no, name, value FROM test_delete
no name value -------------------- ---------- ----------- 1 李四 80 1 王五 80 2 王五 80 1 張三 100 2 張三 100 1 趙六 70 1 趙六 90
--創(chuàng)建視圖
復(fù)制代碼 代碼如下:
CREATE VIEW tmp_view AS SELECT ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no, name, value FROM test_delete
--刪除數(shù)據(jù) 1> DELETE FROM tmp_view WHERE no != 1 2> go
(2 行受影響)
-- 核對結(jié)果 1> m.survivalescaperooms.com 2> select * from test_delete; 3> go name value ---------- ----------- 張三 100 李四 80 王五 80 趙六 90 趙六 70
