SQL查询重复处理记录

1. --1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理) 
3. --> --> (Roy)生成測試數據 
5. if not object_id('Tempdb..#T') is null 
6.     drop table #T 
7. Go 
8. Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2)) 
9. Insert #T 
10. select 1,N'A',N'A1' union all 
11. select 2,N'A',N'A2' union all 
12. select 3,N'A',N'A3' union all 
13. select 4,N'B',N'B1' union all 
14. select 5,N'B',N'B2' 
15. Go 
18. --I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2 
19. 方法1: 
20. Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID <a.ID) 
22. 方法2: 
23. 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 
25. 方法3: 
26. select * from #T a where ID=(select min(ID) from #T where Name=a.Name) 
28. 方法4: 
29. 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 
31. 方法5: 
32. select * from #T a group by ID,Name,Memo having ID=(select min(ID)from #T where Name=a.Name) 
34. 方法6: 
35. select * from #T a where (select count(1) from #T where Name=a.Name and ID <a.ID)=0 
37. 方法7: 
38. select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID) 
40. 方法8: 
41. select * from #T a where ID!>all(select ID from #T where Name=a.Name) 
43. 方法9(注:ID为唯一时可用): 
44. select * from #T a where ID in(select min(ID) from #T group by Name) 
46. --SQL2005: 
48. 方法10: 
49. select ID,Name,Memo from (select *,min(ID)over(partition by Name) as MinID from #T a)T where ID=MinID 
51. 方法11: 
53. select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID) as MinID from #T a)T where MinID=1 
55. 生成结果: 
56. /* 
57. ID          Name Memo 
58. ----------- ---- ---- 
59. 1          A    A1 
60. 4          B    B1 
62. (2 行受影响) 
63. */ 
66. --II、Name相同ID最大的记录,与min相反: 
67. 方法1: 
68. Select * from #T a where not exists(select 1 from #T where Name=a.Name and ID>a.ID) 
70. 方法2: 
71. 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 
73. 方法3: 
74. select * from #T a where ID=(select max(ID) from #T where Name=a.Name) order by ID 
76. 方法4: 
77. 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 
79. 方法5: 
80. select * from #T a group by ID,Name,Memo having ID=(select max(ID)from #T where Name=a.Name) 
82. 方法6: 
83. select * from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)=0 
85. 方法7: 
86. select * from #T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc) 
88. 方法8: 
89. select * from #T a where ID! <all(select ID from #T where Name=a.Name) 
91. 方法9(注:ID为唯一时可用): 
92. select * from #T a where ID in(select max(ID) from #T group by Name) 
94. --SQL2005: 
96. 方法10: 
97. select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID 
99. 方法11: 
100. 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 
102. 生成结果2: 
103. /* 
104. ID          Name Memo 
105. ----------- ---- ---- 
106. 3          A    A3 
107. 5          B    B2 
109. (2 行受影响) 
110. */ 
113. --2、删除重复记录有大小关系时,保留大或小其中一个记录 
116. --> --> (Roy)生成測試數據 
118. if not object_id('Tempdb..#T') is null 
119.     drop table #T 
120. Go 
121. Create table #T([ID] int,[Name] nvarchar(1),[Memo] nvarchar(2)) 
122. Insert #T 
123. select 1,N'A',N'A1' union all 
124. select 2,N'A',N'A2' union all 
125. select 3,N'A',N'A3' union all 
126. select 4,N'B',N'B1' union all 
127. select 5,N'B',N'B2' 
128. Go 
130. --I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条 
131. 方法1: 
132. delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID <a.ID) 
134. 方法2: 
135. 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 
137. 方法3: 
138. delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name) 
140. 方法4(注:ID为唯一时可用): 
141. delete a from #T a where ID not in(select min(ID)from #T group by Name) 
143. 方法5: 
144. delete a from #T a where (select count(1) from #T where Name=a.Name and ID <a.ID)>0 
146. 方法6: 
147. delete a from #T a where ID <>(select top 1 ID from #T where Name=a.name order by ID) 
149. 方法7: 
150. delete a from #T a where ID>any(select ID from #T where Name=a.Name) 
153. select * from #T 
155. 生成结果: 
156. /* 
157. ID          Name Memo 
158. ----------- ---- ---- 
159. 1          A    A1 
160. 4          B    B1 
162. (2 行受影响) 
163. */ 
166. --II、Name相同ID保留最大的一条记录: 
168. 方法1: 
169. delete a from #T a where  exists(select 1 from #T where Name=a.Name and ID>a.ID) 
171. 方法2: 
172. 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 
174. 方法3: 
175. delete a from #T a where ID not in (select max(ID) from #T where Name=a.Name) 
177. 方法4(注:ID为唯一时可用): 
178. delete a from #T a where ID not in(select max(ID)from #T group by Name) 
180. 方法5: 
181. delete a from #T a where (select count(1) from #T where Name=a.Name and ID>a.ID)>0 
183. 方法6: 
184. delete a from #T a where ID <>(select top 1 ID from #T where Name=a.name order by ID desc) 
186. 方法7: 
187. delete a from #T a where ID <any(select ID from #T where Name=a.Name) 
190. select * from #T 
191. /* 
192. ID          Name Memo 
193. ----------- ---- ---- 
194. 3          A    A3 
195. 5          B    B2 
197. (2 行受影响) 
198. */ 
201. --3、删除重复记录没有大小关系时，处理重复值 
204. --> --> (Roy)生成測試數據 
206. if not object_id('Tempdb..#T') is null 
207.     drop table #T 
208. Go 
209. Create table #T([Num] int,[Name] nvarchar(1)) 
210. Insert #T 
211. select 1,N'A' union all 
212. select 1,N'A' union all 
213. select 1,N'A' union all 
214. select 2,N'B' union all 
215. select 2,N'B' 
216. Go 
218. 方法1: 
219. if object_id('Tempdb..#') is not null 
220.     drop table # 
221. Select distinct * into # from #T--排除重复记录结果集生成临时表# 
223. truncate table #T--清空表 
225. insert #T select * from #    --把临时表#插入到表#T中 
227. --查看结果 
228. select * from #T 
230. /* 
231. Num        Name 
232. ----------- ---- 
233. 1          A 
234. 2          B 
236. (2 行受影响) 
237. */ 
239. --重新执行测试数据后用方法2 
240. 方法2: 
242. alter table #T add ID int identity--新增标识列 
243. go 
244. delete a from  #T a where  exists(select 1 from #T where Num=a.Num and Name=a.Name and ID>a.ID)--只保留一条记录 
245. go 
246. alter table #T drop column ID--删除标识列 
248. --查看结果 
249. select * from #T 
251. /* 
252. Num        Name 
253. ----------- ---- 
254. 1          A 
255. 2          B 
257. (2 行受影响) 
259. */ 
261. --重新执行测试数据后用方法3 
262. 方法3: 
263. declare Roy_Cursor cursor local for 
264. select count(1)-1,Num,Name from #T group by Num,Name having count(1)>1 
265. declare @con int,@Num int,@Name nvarchar(1) 
266. open Roy_Cursor 
267. fetch next from Roy_Cursor into @con,@Num,@Name 
268. while @@Fetch_status=0 
269. begin 
270.     set rowcount @con; 
271.     delete #T where Num=@Num and Name=@Name 
272.     set rowcount 0; 
273.     fetch next from Roy_Cursor into @con,@Num,@Name 
274. end 
275. close Roy_Cursor 
276. deallocate Roy_Cursor 
278. --查看结果 
279. select * from #T 
280. /* 
281. Num        Name 
282. ----------- ---- 
283. 1          A 
284. 2          B 
286. (2 行受影响) 
288. --------------------------
289. --按某一字段分组取最大(小)值所在行的数据
290. (爱新觉罗.毓华 2007-10-23于浙江杭州)
291. /*
292. 数据如下：
293. name val memo
294. a    2   a2(a的第二个值)
295. a    1   a1--a的第一个值
296. a    3   a3:a的第三个值
297. b    1   b1--b的第一个值
298. b    3   b3:b的第三个值
299. b    2   b2b2b2b2
300. b    4   b4b4
301. b    5   b5b5b5b5b5
302. */
303. --创建表并插入数据：
304. create table tb(name varchar(10),val int,memo varchar(20))
305. insert into tb values('a',    2,   'a2(a的第二个值)')
306. insert into tb values('a',    1,   'a1--a的第一个值')
307. insert into tb values('a',    3,   'a3:a的第三个值')
308. insert into tb values('b',    1,   'b1--b的第一个值')
309. insert into tb values('b',    3,   'b3:b的第三个值')
310. insert into tb values('b',    2,   'b2b2b2b2')
311. insert into tb values('b',    4,   'b4b4')
312. insert into tb values('b',    5,   'b5b5b5b5b5')
313. go
315. --一、按name分组取val最大的值所在行的数据。
316. --方法1：
317. select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
318. --方法2：
319. select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val)
320. --方法3：
321. select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
322. --方法4：
323. select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
324. --方法5
325. select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name
326. /*
327. name       val         memo                 
328. ---------- ----------- -------------------- 
329. a          3           a3:a的第三个值
330. b          5           b5b5b5b5b5
331. */
333. --二、按name分组取val最小的值所在行的数据。
334. --方法1：
335. select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
336. --方法2：
337. select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val)
338. --方法3：
339. select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name
340. --方法4：
341. select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name
342. --方法5
343. select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name
344. /*
345. name       val         memo                 
346. ---------- ----------- -------------------- 
347. a          1           a1--a的第一个值
348. b          1           b1--b的第一个值
349. */
351. --三、按name分组取第一次出现的行所在的数据。
352. select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name
353. /*
354. name       val         memo                 
355. ---------- ----------- -------------------- 
356. a          2           a2(a的第二个值)
357. b          1           b1--b的第一个值
358. */
360. --四、按name分组随机取一条数据。
361. select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name
362. /*
363. name       val         memo                 
364. ---------- ----------- -------------------- 
365. a          1           a1--a的第一个值
366. b          5           b5b5b5b5b5
367. */
369. --五、按name分组取最小的两个(N个)val
370. select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.val
371. select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val
372. select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name
373. /*
374. name       val         memo                 
375. ---------- ----------- -------------------- 
376. a          1           a1--a的第一个值
377. a          2           a2(a的第二个值)
378. b          1           b1--b的第一个值
379. b          2           b2b2b2b2
380. */
382. --六、按name分组取最大的两个(N个)val
383. select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
384. select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
385. select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name
386. /*
387. name       val         memo                 
388. ---------- ----------- -------------------- 
389. a          2           a2(a的第二个值)
390. a          3           a3:a的第三个值
391. b          4           b4b4
392. b          5           b5b5b5b5b5
393. */
394. --七，如果整行数据有重复，所有的列都相同。
395. /*
396. 数据如下：
397. name val memo
398. a    2   a2(a的第二个值)
399. a    1   a1--a的第一个值
400. a    1   a1--a的第一个值
401. a    3   a3:a的第三个值
402. a    3   a3:a的第三个值
403. b    1   b1--b的第一个值
404. b    3   b3:b的第三个值
405. b    2   b2b2b2b2
406. b    4   b4b4
407. b    5   b5b5b5b5b5
408. */
409. --在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
410. --创建表并插入数据：
411. create table tb(name varchar(10),val int,memo varchar(20))
412. insert into tb values('a',    2,   'a2(a的第二个值)')
413. insert into tb values('a',    1,   'a1--a的第一个值')
414. insert into tb values('a',    1,   'a1--a的第一个值')
415. insert into tb values('a',    3,   'a3:a的第三个值')
416. insert into tb values('a',    3,   'a3:a的第三个值')
417. insert into tb values('b',    1,   'b1--b的第一个值')
418. insert into tb values('b',    3,   'b3:b的第三个值')
419. insert into tb values('b',    2,   'b2b2b2b2')
420. insert into tb values('b',    4,   'b4b4')
421. insert into tb values('b',    5,   'b5b5b5b5b5')
422. go
424. select * , px = identity(int,1,1) into tmp from tb
426. select m.name,m.val,m.memo from
427. (
428.   select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
429. ) m where px = (select min(px) from
430. (
431.   select t.* from tmp t where val = (select min(val) from tmp where name = t.name)
432. ) n where n.name = m.name)
434. drop table tb,tmp
436. /*
437. name       val         memo
438. ---------- ----------- --------------------
439. a          1           a1--a的第一个值
440. b          1           b1--b的第一个值
442. (2 行受影响)
443. */
444. --在sql server 2005中可以使用row_number函数，不需要使用临时表。
445. --创建表并插入数据：
446. create table tb(name varchar(10),val int,memo varchar(20))
447. insert into tb values('a',    2,   'a2(a的第二个值)')
448. insert into tb values('a',    1,   'a1--a的第一个值')
449. insert into tb values('a',    1,   'a1--a的第一个值')
450. insert into tb values('a',    3,   'a3:a的第三个值')
451. insert into tb values('a',    3,   'a3:a的第三个值')
452. insert into tb values('b',    1,   'b1--b的第一个值')
453. insert into tb values('b',    3,   'b3:b的第三个值')
454. insert into tb values('b',    2,   'b2b2b2b2')
455. insert into tb values('b',    4,   'b4b4')
456. insert into tb values('b',    5,   'b5b5b5b5b5')
457. go
459. select m.name,m.val,m.memo from
460. (
461.   select * , px = row_number() over(order by name , val) from tb
462. ) m where px = (select min(px) from
463. (
464.   select * , px = row_number() over(order by name , val) from tb
465. ) n where n.name = m.name)
467. drop table tb
469. /*
470. name       val         memo
471. ---------- ----------- --------------------
472. a          1           a1--a的第一个值
473. b          1           b1--b的第一个值
475. (2 行受影响)
476. */
478. select * from @T t where userid=(select min(userid) from @T where left(loginname,3)=left(t.loginname,3))
480. ----------------------------------------------------
481. --------------删除所有重复记录 
483. delete a where col1 in (select col1 from a group by col001 having count(*)>1) 
485. ------------------全部重复 保留一条数据， 
487. select * into #b from a ---备份 
489. truncate table #b 
491. insert into a 
492. select disitnct  * from #b 
495. ---------------某个字段重复，只保留一条数据库（用游标） 
497. -------------------------用游标过滤重复记录 
498. declare @aa varchar(60) 
499. declare www cursor for 
500. select h_name from join_product_ty_bf 
501. open www 
502. fetch next from www into @aa 
503. while(@@fetch_status=0) 
504. begin 
505. fetch next from www into @aa 
506. if exists 
507. (select h_name from join_product_ty_bf where h_name like @aa group by h_name  having count(*)>1) 
508. delete join_product_ty_bf where current of www 
509. end