資料庫黑洞

這個故事是發生在一個沒有建立 PrimaryKey的資料表上，很不巧的，剛好這個資料表是紀錄登入帳號的表，因為沒有 PK ，也沒有建立 Unique Index ，所以重複建立資料時，在讀取就發生悲劇了，以下模擬重複資料的情況：

 

-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-02
-- Update date: 2014-06-02
-- Description:	建立一個沒有 PrimaryKey的資料表，並寫入重複資料
-- Require: none
-- =============================================
--存在則刪除
IF OBJECT_ID('NoPrimaryKey') IS NOT NULL
	DROP TABLE NoPrimaryKey;
GO
--創建資料表
CREATE TABLE NoPrimaryKey
(
	ID INT NOT NULL,
	Name NVARCHAR(20) NOT NULL
);
--輸入測試資料
INSERT INTO NoPrimaryKey (ID, Name)
VALUES
	(1, 'OK'),
	(1, 'OK'),
	(2, 'OK');
--查詢
SELECT * FROM NoPrimaryKey;

 

查詢結果如下：

 

由於資料一樣，嘗試用下列語法刪除，都達不到我想只保留的一筆重複資料的效果

-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-02
-- Update date: 2014-06-02
-- Description:	嘗試刪除多餘重複資料，但沒有成功 (如果有 CTE 以外的語法能達到，還請各位大大提供)
-- Require: none
-- =============================================
--1.無法正確刪除多餘重複資料
DELETE FROM [dbo].[NoPrimaryKey] WHERE ID = 1;

--2.無法正確刪除多餘重複資料
DELETE FROM [dbo].[NoPrimaryKey]
WHERE ID IN (
	SELECT TOP 1 ID
	FROM [dbo].[NoPrimaryKey]
	WHERE ID = 1
);

 

打開編輯畫面直接對重複資料做刪除，也一樣出錯

 

 

這時亮出 CTE ，可輕鬆解決現有的囧境。

-- Author: Henry
-- Author URL: http://www.dotblogs.com.tw/henryli/
-- Script Version: 1.0
-- MSSQL Version: 2005+
-- Create date: 2014-06-02
-- Update date: 2014-06-02
-- Description:	使用 CTE 刪除多餘的重複資料
-- Require: none
-- =============================================
;WITH NoRepeatData AS (
	SELECT ID, Name, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RowNum
	FROM [dbo].[NoPrimaryKey]
)
DELETE FROM NoRepeatData
WHERE RowNum > 1;

--查詢
SELECT * FROM NoPrimaryKey;

 

資料正常。

 

 

參考資料：

使用 TOP 來限制刪除的資料列

 

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~