中国风整理贴——处理表重复记录(查询和删除)

1. --处理表重复记录(查询和删除)
2. /******************************************************************************************************************************************************
3. 1、Num、Name相同的重复值记录,没有大小关系只保留一条
4. 2、Name相同,ID有大小关系时,保留大或小其中一个记录
5. 整理人：中国风(Roy)
6.  
7. 日期:2008.06.06
8. ******************************************************************************************************************************************************/
9.  
10. --1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)
11.  
12. --> --> (Roy)生成測試數據
13.  
14. IF not OBJECT_ID ( 'Tempdb..#T' ) IS null
15.     DROP TABLE #T
16. Go
17. CREATE TABLE #T ([ID ] INT, [Name ] NVARCHAR ( 1 ), [Memo ] NVARCHAR ( 2 ))
18. INSERT #T
19. SELECT 1,N 'A',N 'A1' UNION all
20. SELECT 2,N 'A',N 'A2' UNION all
21. SELECT 3,N 'A',N 'A3' UNION all
22. SELECT 4,N 'B',N 'B1' UNION all
23. SELECT 5,N 'B',N 'B2'
24. Go
25.  
26.  
27. --I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
28. 方法 1:
29. SELECT * FROM #T a WHERE not exists ( SELECT 1 FROM #T WHERE Name =a. Name and ID <a. ID )
30.  
31. 方法 2:
32. SELECT a. * FROM #T a join ( SELECT MIN (ID )ID,Name FROM #T GROUP BY Name ) b ON a. Name =b. Name and a. ID =b. ID
33.  
34. 方法 3:
35. SELECT * FROM #T a WHERE ID =( SELECT MIN (ID ) FROM #T WHERE Name =a. Name )
36.  
37. 方法 4:
38. SELECT a. * FROM #T a join #T b ON a. Name =b. Name and a. ID >=b. ID GROUP BY a. ID,a. Name,a. Memo HAVING COUNT ( 1 )= 1
39.  
40. 方法 5:
41. SELECT * FROM #T a GROUP BY ID,Name,Memo HAVING ID =( SELECT MIN (ID ) FROM #T WHERE Name =a. Name )
42.  
43. 方法 6:
44. SELECT * FROM #T a WHERE ( SELECT COUNT ( 1 ) FROM #T WHERE Name =a. Name and ID <a. ID )= 0
45.  
46. 方法 7:
47. SELECT * FROM #T a WHERE ID =( SELECT TOP 1 ID FROM #T WHERE Name =a. name ORDER BY ID )
48.  
49. 方法 8:
50. SELECT * FROM #T a WHERE ID !>all ( SELECT ID FROM #T WHERE Name =a. Name )
51.  
52. 方法 9 (注:ID为唯一时可用 ):
53. SELECT * FROM #T a WHERE ID in ( SELECT MIN (ID ) FROM #T GROUP BY Name )
54.  
55. --SQL2005:
56.  
57. 方法 10:
58. SELECT ID,Name,Memo FROM ( SELECT *, MIN (ID ) OVER (partition BY Name ) AS MinID FROM #T a )T WHERE ID =MinID
59.  
60. 方法 11:
61.  
62. SELECT ID,Name,Memo FROM ( SELECT *,row_number () OVER (partition BY Name ORDER BY ID ) AS MinID FROM #T a )T WHERE MinID = 1
63.  
64. 生成结果:
65. /*
66. ID          Name Memo
67. ----------- ---- ----
68. 1           A    A1
69. 4           B    B1
70.  
71. (2 行受影响)
72. */
73.  
74.  
75. --II、Name相同ID最大的记录,与min相反:
76. 方法 1:
77. SELECT * FROM #T a WHERE not exists ( SELECT 1 FROM #T WHERE Name =a. Name and ID >a. ID )
78.  
79. 方法 2:
80. SELECT a. * FROM #T a join ( SELECT MAX (ID )ID,Name FROM #T GROUP BY Name ) b ON a. Name =b. Name and a. ID =b. ID ORDER BY ID
81.  
82. 方法 3:
83. SELECT * FROM #T a WHERE ID =( SELECT MAX (ID ) FROM #T WHERE Name =a. Name ) ORDER BY ID
84.  
85. 方法 4:
86. SELECT a. * FROM #T a join #T b ON a. Name =b. Name and a. ID <=b. ID GROUP BY a. ID,a. Name,a. Memo HAVING COUNT ( 1 )= 1
87.  
88. 方法 5:
89. SELECT * FROM #T a GROUP BY ID,Name,Memo HAVING ID =( SELECT MAX (ID ) FROM #T WHERE Name =a. Name )
90.  
91. 方法 6:
92. SELECT * FROM #T a WHERE ( SELECT COUNT ( 1 ) FROM #T WHERE Name =a. Name and ID >a. ID )= 0
93.  
94. 方法 7:
95. SELECT * FROM #T a WHERE ID =( SELECT TOP 1 ID FROM #T WHERE Name =a. name ORDER BY ID DESC )
96.  
97. 方法 8:
98. SELECT * FROM #T a WHERE ID !<all ( SELECT ID FROM #T WHERE Name =a. Name )
99.  
100. 方法 9 (注:ID为唯一时可用 ):
101. SELECT * FROM #T a WHERE ID in ( SELECT MAX (ID ) FROM #T GROUP BY Name )
102.  
103. --SQL2005:
104.  
105. 方法 10:
106. SELECT ID,Name,Memo FROM ( SELECT *, MAX (ID ) OVER (partition BY Name ) AS MinID FROM #T a )T WHERE ID =MinID
107.  
108. 方法 11:
109. SELECT ID,Name,Memo FROM ( SELECT *,row_number () OVER (partition BY Name ORDER BY ID DESC ) AS MinID FROM #T a )T WHERE MinID = 1
110.  
111. 生成结果 2:
112. /*
113. ID          Name Memo
114. ----------- ---- ----
115. 3           A    A3
116. 5           B    B2
117.  
118. (2 行受影响)
119. */
120.  
121.  
122.  
123. --2、删除重复记录有大小关系时,保留大或小其中一个记录
124.  
125.  
126. --> --> (Roy)生成測試數據
127.  
128. IF not OBJECT_ID ( 'Tempdb..#T' ) IS null
129.     DROP TABLE #T
130. Go
131. CREATE TABLE #T ([ID ] INT, [Name ] NVARCHAR ( 1 ), [Memo ] NVARCHAR ( 2 ))
132. INSERT #T
133. SELECT 1,N 'A',N 'A1' UNION all
134. SELECT 2,N 'A',N 'A2' UNION all
135. SELECT 3,N 'A',N 'A3' UNION all
136. SELECT 4,N 'B',N 'B1' UNION all
137. SELECT 5,N 'B',N 'B2'
138. Go
139.  
140. --I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
141. 方法 1:
142. DELETE a FROM #T a WHERE  exists ( SELECT 1 FROM #T WHERE Name =a. Name and ID <a. ID )
143.  
144. 方法 2:
145. DELETE a   FROM #T a LEFT join ( SELECT MIN (ID )ID,Name FROM #T GROUP BY Name ) b ON a. Name =b. Name and a. ID =b. ID WHERE b. Id IS null
146.  
147. 方法 3:
148. DELETE a FROM #T a WHERE ID not in ( SELECT MIN (ID ) FROM #T WHERE Name =a. Name )
149.  
150. 方法 4 (注:ID为唯一时可用 ):
151. DELETE a FROM #T a WHERE ID not in ( SELECT MIN (ID ) FROM #T GROUP BY Name )
152.  
153. 方法 5:
154. DELETE a FROM #T a WHERE ( SELECT COUNT ( 1 ) FROM #T WHERE Name =a. Name and ID <a. ID )> 0
155.  
156. 方法 6:
157. DELETE a FROM #T a WHERE ID <>( SELECT TOP 1 ID FROM #T WHERE Name =a. name ORDER BY ID )
158.  
159. 方法 7:
160. DELETE a FROM #T a WHERE ID >any ( SELECT ID FROM #T WHERE Name =a. Name )
161.  
162.  
163.  
164. SELECT * FROM #T
165.  
166. 生成结果:
167. /*
168. ID          Name Memo
169. ----------- ---- ----
170. 1           A    A1
171. 4           B    B1
172.  
173. (2 行受影响)
174. */
175.  
176.  
177. --II、Name相同ID保留最大的一条记录:
178.  
179. 方法 1:
180. DELETE a FROM #T a WHERE  exists ( SELECT 1 FROM #T WHERE Name =a. Name and ID >a. ID )
181.  
182. 方法 2:
183. DELETE a   FROM #T a LEFT join ( SELECT MAX (ID )ID,Name FROM #T GROUP BY Name ) b ON a. Name =b. Name and a. ID =b. ID WHERE b. Id IS null
184.  
185. 方法 3:
186. DELETE a FROM #T a WHERE ID not in ( SELECT MAX (ID ) FROM #T WHERE Name =a. Name )
187.  
188. 方法 4 (注:ID为唯一时可用 ):
189. DELETE a FROM #T a WHERE ID not in ( SELECT MAX (ID ) FROM #T GROUP BY Name )
190.  
191. 方法 5:
192. DELETE a FROM #T a WHERE ( SELECT COUNT ( 1 ) FROM #T WHERE Name =a. Name and ID >a. ID )> 0
193.  
194. 方法 6:
195. DELETE a FROM #T a WHERE ID <>( SELECT TOP 1 ID FROM #T WHERE Name =a. name ORDER BY ID DESC )
196.  
197. 方法 7:
198. DELETE a FROM #T a WHERE ID <any ( SELECT ID FROM #T WHERE Name =a. Name )
199.  
200.  
201. SELECT * FROM #T
202. /*
203. ID          Name Memo
204. ----------- ---- ----
205. 3           A    A3
206. 5           B    B2
207.  
208. (2 行受影响)
209. */
210.  
211.  
212.  
213.  
214.  
215. --3、删除重复记录没有大小关系时，处理重复值
216.  
217.  
218. --> --> (Roy)生成測試數據
219.  
220. IF not OBJECT_ID ( 'Tempdb..#T' ) IS null
221.     DROP TABLE #T
222. Go
223. CREATE TABLE #T ([Num ] INT, [Name ] NVARCHAR ( 1 ))
224. INSERT #T
225. SELECT 1,N 'A' UNION all
226. SELECT 1,N 'A' UNION all
227. SELECT 1,N 'A' UNION all
228. SELECT 2,N 'B' UNION all
229. SELECT 2,N 'B'
230. Go
231.  
232. 方法 1:
233. IF OBJECT_ID ( 'Tempdb..#' ) IS not null
234.     DROP TABLE #
235. SELECT DISTINCT * INTO # FROM #T --排除重复记录结果集生成临时表#
236.  
237. TRUNCATE TABLE #T --清空表
238.  
239. INSERT #T SELECT * FROM #     --把临时表#插入到表#T中
240.  
241. --查看结果
242. SELECT * FROM #T
243.  
244. /*
245. Num         Name
246. ----------- ----
247. 1           A
248. 2           B
249.  
250. (2 行受影响)
251. */
252.  
253. --重新执行测试数据后用方法2
254. 方法 2:
255.  
256. ALTER TABLE #T ADD ID INT IDENTITY --新增标识列
257. go
258. DELETE a FROM  #T a WHERE  exists ( SELECT 1 FROM #T WHERE Num =a. Num and Name =a. Name and ID >a. ID ) --只保留一条记录
259. go
260. ALTER TABLE #T DROP COLUMN ID --删除标识列
261.  
262. --查看结果
263. SELECT * FROM #T
264.  
265. /*
266. Num         Name
267. ----------- ----
268. 1           A
269. 2           B
270.  
271. (2 行受影响)
272.  
273. */
274.  
275. --重新执行测试数据后用方法3
276. 方法 3:
277. DECLARE Roy_Cursor CURSOR LOCAL FOR
278. SELECT COUNT ( 1 ) -1,Num,Name FROM #T GROUP BY Num,Name HAVING COUNT ( 1 )> 1
279. DECLARE @con INT,@Num INT,@Name NVARCHAR ( 1 )
280. OPEN Roy_Cursor
281. FETCH NEXT FROM Roy_Cursor INTO @con,@Num,@Name
282. WHILE @@FETCH_STATUS = 0
283. BEGIN
284.     SET rowcount @con;
285.     DELETE #T WHERE Num =@Num and Name =@Name
286.     SET rowcount 0;
287.     FETCH NEXT FROM Roy_Cursor INTO @con,@Num,@Name
288. END
289. CLOSE Roy_Cursor
290. DEALLOCATE Roy_Cursor
291.  
292. --查看结果
293. SELECT * FROM #T
294. /*
295. Num         Name
296. ----------- ----
297. 1           A
298. 2           B
299.  
300. (2 行受影响)
301. */