[SQL]刪除資料庫內重覆的資料(MS SQL、Oracle)

2014-07-17

1105
0
2015-04-09

摘要:[SQL]刪除資料庫內重覆的資料(MS SQL、Oracle)

一、前言

今天在查看user回報的系統問題，發現是一些資料重覆的問題，應該是之前不小心倒了2次資料，又有一些table是沒有到primary key，才會造成資料重覆

所以特別去查看怎麼刪除重覆的資料

主要參考的文章就是：
http://sqlqna.blogspot.tw/2013/10/blog-post_9.html
另外也看到一些其它的用法，不過有點複雜就懶得看，裡面有一篇是比較各刪除方法的效能，有興趣的可以參考看看
效能比較http://www.dotblogs.com.tw/ricochen/archive/2011/08/26/34462.aspx
另外一篇刪除sql語法http://www.dotblogs.com.tw/sim3ds/archive/2011/08/26/34412.aspx

二、本文

1.首先建立一個Employee table來測試

/****** Object:  Table [dbo].[Employee]    Script Date: 2014/7/17 上午 02:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Employee_AndrewTest](
	[EmployeeId] [int] NULL,
	[EmployeeName] [nvarchar](50) NULL,
        [DeptNo] [int] NULL,
        [DeptName] [nvarchar](50) NULL
) ON [PRIMARY]

GO

2‧建立重覆的測試資料

INSERT INTO Employee_AndrewTest VALUES (1,'Andrew',1,'RD1')

go 100

INSERT INTO Employee_AndrewTest VALUES (2,'Bruce',2,'RD2');

go 50

INSERT INTO Employee_AndrewTest VALUES (3,'Claire',3,'RD3')

go 20

3.刪除重覆資料sql

(1)方法1

DELETE FROM Employee_AndrewTest T1

WHERE %%physloc%%>

(

SELECT MIN(%%physloc%%)

FROM Employee_AndrewTest T2

WHERE (1=1)

AND T1.EmployeeId=T2.EmployeeId

GROUP BY EmployeeId,EmployeeName

)

(2)方法2

WITH Test

(

SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeName ORDER BY EmployeeId) seq

FROM muchnewdb.dbo.Employee_AndrewTest

)

DELETE FROM Test WHERE seq>1

．With
-google查sql with cte==>http://www.flag.com.tw/book/cento-5105.asp?bokno=FS232&id=419

一般資料表運算式(Common Table Expressions,CTE)

． ROW_NUMBER() OVER (PARTITION BY EmployeeName ORDER BY EmployeeId)

指由EmployeeId依照EmployeeId排序，而依照EmployeeName 做排序，意思就是名字不一樣，排序會重算

而PARTITION BY跟ORDER BY都可以多個欄位，以此範例的話，Andrew的seq會由1~100，Bruce是1~50，Claire則是1~20

回首頁