sql查詢案例:刪除2條完全一樣的資料

刪除2條完全一樣的資料

今天百度知道上面，有看到一個帖，說要求：
2條完全一樣的資料刪除一條保留一條，用SQL語句刪除。

 

SQL Server 的處理辦法

 

-- 首先建立測試表
CREATE TABLE test_delete(
  name  varchar(10),
  value INT
);
go

-- 測試資料，其中 張三100 與 王五80 是完全一樣的
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

-- 首先查詢一下， ROW_NUMBER 效果是否滿足預期
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

從結果上可以看到，如果有重複的，完全一樣的話， no 是有大於1的。

-- 建立檢視
CREATE VIEW tmp_view AS
SELECT
  ROW_NUMBER() OVER (PARTITION BY name, value ORDER BY (SELECT 1) ) AS no,
  name,
  value
FROM
  test_delete

-- 刪除 檢視中的 no 不等於 1 的資料。
1> DELETE FROM tmp_view WHERE no != 1
2> go

(2 行受影響)
1>
2> select * from test_delete;
3> go
name       value
---------- -----------
張三                 100
李四                  80
王五                  80
趙六                  90
趙六                  70

(5 行受影響)

結果看上去是滿足預期的。

 

Oracle 的處理辦法

使用 Oracle 的  ROWID 來刪除的處理步驟如下：

 

SQL> CREATE TABLE test_delete(
  2    name  varchar(10),
  3    value INT
  4  );
表已建立。

 

SQL> INSERT INTO test_delete
  2  SELECT '張三', 100 FROM dual
  3  UNION ALL SELECT '張三', 100 FROM dual
  4  UNION ALL SELECT '李四', 80  FROM dual
  5  UNION ALL SELECT '王五', 80  FROM dual
  6  UNION ALL SELECT '王五', 80  FROM dual
  7  UNION ALL SELECT '趙六', 90  FROM dual
  8  UNION ALL SELECT '趙六', 70  FROM dual;
已建立7行。

 

SQL> SELECT
  2    ROWID,
  3    name,
  4    value
  5  FROM
  6    test_delete;
ROWID              NAME            VALUE
------------------ ---------- ----------
AAAM2mAAGAAAAOXAAA 張三              100
AAAM2mAAGAAAAOXAAB 張三              100
AAAM2mAAGAAAAOXAAC 李四               80
AAAM2mAAGAAAAOXAAD 王五               80
AAAM2mAAGAAAAOXAAE 王五               80
AAAM2mAAGAAAAOXAAF 趙六               90
AAAM2mAAGAAAAOXAAG 趙六               70
已選擇7行。

 

SQL> DELETE
  2    test_delete
  3  WHERE
  4    (name, value) 
  5      IN (SELECT 
  6            name, value 
  7          FROM 
  8            test_delete
  9          GROUP BY
 10            name, value
 11          HAVING COUNT(1) > 1)
 12      AND rowid NOT IN 
 13         (SELECT 
 14            MIN(rowid)
 15          FROM
 16            test_delete
 17          GROUP BY
 18            name, value
 19          HAVING
 20            COUNT(1) > 1);

已刪除2行。

SQL> SELECT
  2    ROWID,
  3    name,
  4    value
  5  FROM
  6    test_delete;

ROWID              NAME            VALUE
------------------ ---------- ----------
AAAM2mAAGAAAAOXAAA 張三              100
AAAM2mAAGAAAAOXAAC 李四               80
AAAM2mAAGAAAAOXAAD 王五               80
AAAM2mAAGAAAAOXAAF 趙六               90
AAAM2mAAGAAAAOXAAG 趙六               70