根据条件，删除重复字段的记录？
RC_ID RC_DeviceID RC_TimeNo
1 1 1
2 1 2
3 1 3
3 1 3
4 1 4
5 1 4
6 1 5
7 2 1
8 2 2
9 2 3
10 2 4
...
说明：RC_ID为主键，删除RC_DeviceID=1 and RC_TimeNo=重复的记录。
最终结果
RC_ID RC_DeviceID RC_TimeNo
1 1 1
2 1 2
3 1 3
5 1 4
6 1 5
7 2 1
8 2 2
9 2 3
10 2 4
...
[解决办法]
if object_id('tb')is not null drop table tbgoCREATE TABLE tb(RC_ID int, RC_DeviceID int, RC_TimeNo int)INSERT tb SELECT1 , 1 , 1 UNION ALL SELECT 2 , 1 , 2 UNION ALL SELECT 3 , 1 , 3 UNION ALL SELECT 3 , 1 , 3 UNION ALL SELECT 4 , 1 , 4 UNION ALL SELECT 5 , 1 , 4 UNION ALL SELECT 6 , 1 , 5 UNION ALL SELECT 7 , 2 , 1 UNION ALL SELECT 8 , 2 , 2 UNION ALL SELECT 9 , 2 , 3 UNION ALL SELECT 10 , 2 , 4 select * ,id=identity(int,1,1) into #t from tbtruncate table tbinsert tb select RC_ID, RC_DeviceID , RC_TimeNo from #t twhere not exists(select * from #t where RC_DeviceID =t.RC_DeviceID and RC_TimeNo=t.RC_TimeNo and id>t.id)select * from tbRC_ID RC_DeviceID RC_TimeNo----------- ----------- -----------1 1 12 1 23 1 35 1 46 1 57 2 18 2 29 2 310 2 4(9 行受影响)drop table #t
[解决办法]
/*Micsosoft Windows 7.0 7600Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 小弟愿和大家共同进步、共同学习！ 如有雷同、实属巧合●●●●●2009-09-11 17:54:44.950●●●●●★★★★★soft_wsx★★★★★*/if object_id('tb')is not null drop table tbgoCREATE TABLE tb(RC_ID int, RC_DeviceID int, RC_TimeNo int)INSERT tb SELECT1 , 1 , 1 UNION ALL SELECT 2 , 1 , 2 UNION ALL SELECT 3 , 1 , 3 UNION ALL SELECT 3 , 1 , 3 UNION ALL SELECT 4 , 1 , 4 UNION ALL SELECT 5 , 1 , 4 UNION ALL SELECT 6 , 1 , 5 UNION ALL SELECT 7 , 2 , 1 UNION ALL SELECT 8 , 2 , 2 UNION ALL SELECT 9 , 2 , 3 UNION ALL SELECT 10 , 2 , 4 select distinct * from tb a where not exists(select 1 from tb where RC_TimeNo=a.RC_TimeNo and RC_DeviceID=a.RC_DeviceID and RC_ID>a.RC_ID) /* RC_ID RC_DeviceID RC_TimeNo1 1 12 1 23 1 35 1 46 1 57 2 18 2 29 2 310 2 4*/
[解决办法]
declare @TB table( RC_ID int, RC_DeviceID int, RC_TimeNo int)insert into @TB select 1,1,1union all select 2,1,2union all select 3,1,3union all select 3,1,3union all select 4,1,4union all select 5,1,5union all select 6,1,5;with hgo as( select *,row_number() over (partition by RC_DeviceID order by RC_TimeNo) rank from @TB)select * from hgo h where not exists(select * from hgo where RC_ID=h.RC_ID and RC_DeviceID=h.RC_DeviceID and RC_TimeNo=h.RC_TimeNo and rank<h.rank)RC_ID RC_DeviceID RC_TimeNo rank----------- ----------- ----------- --------------------1 1 1 12 1 2 23 1 3 34 1 4 55 1 5 66 1 5 7
[解决办法]
--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([RC_ID] int,[RC_DeviceID] int,[RC_TimeNo] int)insert [tb]select 1,1,1 union allselect 2,1,2 union allselect 3,1,3 union allselect 4,1,3 union allselect 5,1,4 union allselect 6,1,4 union allselect 7,1,5 union allselect 8,2,1 union allselect 9,2,2 union allselect 10,2,3 union allselect 11,2,4 delete t from tb t where RC_DeviceID=1 and exists(select 1 from tb where RC_DeviceID=1 and [RC_TimeNo]=t.[RC_TimeNo] and rc_id>t.rc_id)select * from [tb]/*RC_ID RC_DeviceID RC_TimeNo----------- ----------- -----------1 1 12 1 24 1 36 1 47 1 58 2 19 2 210 2 311 2 4(9 行受影响)*/
[解决办法]
/*Micsosoft Windows 7.0 7600Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 小弟愿和大家共同进步、共同学习！ 如有雷同、实属巧合●●●●●2009-09-11 17:54:44.950●●●●●★★★★★soft_wsx★★★★★*/if object_id('tb')is not null drop table tbgoCREATE TABLE tb(RC_ID int identity(1,1) primary key, RC_DeviceID int, RC_TimeNo int)INSERT tb SELECT 1 , 1 UNION ALL SELECT 1 , 2 UNION ALL SELECT 1 , 3 UNION ALL SELECT 1 , 3 UNION ALL SELECT 1 , 4 UNION ALL SELECT 1 , 4 UNION ALL SELECT 1 , 5 UNION ALL SELECT 2 , 1 UNION ALL SELECT 2 , 2 UNION ALL SELECT 2 , 3 UNION ALL SELECT 2 , 4 select * from tb a where not exists(select 1 from tb where RC_TimeNo=a.RC_TimeNo and RC_DeviceID=a.RC_DeviceID and RC_ID>a.RC_ID) select a.* from tb a,(select RC_TimeNo,RC_DeviceID,MAX(rc_id) as id from tb group by RC_TimeNo,RC_DeviceID)b where a.RC_DeviceID=b.RC_DeviceID and a.RC_ID=b.id and a.RC_TimeNo=b.RC_TimeNo order by rc_id /*RC_ID RC_DeviceID RC_TimeNo1 1 12 1 24 1 36 1 47 1 58 2 19 2 210 2 311 2 4*/
[解决办法]
------------------------------ Author :fredrickhu(我是小F，向高手学习)-- Date :2009-09-28 14:41:52-- Version:-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)--------------------------------> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([RC_ID] int,[RC_DeviceID] int,[RC_TimeNo] int)insert [tb]select 1,1,1 union allselect 2,1,2 union allselect 3,1,3 union allselect 3,1,3 union allselect 4,1,4 union allselect 5,1,4 union allselect 6,1,5 union allselect 7,2,1 union allselect 8,2,2 union allselect 9,2,3 union allselect 10,2,4--------------开始查询--------------------------delete t from tb t where RC_DeviceID=1 and exists(select 1 from tb where RC_DeviceID=1 and [RC_TimeNo]=t.[RC_TimeNo] and rc_id>t.rc_id)select * from tb----------------结果----------------------------/* RC_ID RC_DeviceID RC_TimeNo----------- ----------- -----------1 1 12 1 23 1 33 1 35 1 46 1 57 2 18 2 29 2 310 2 4(10 行受影响)*/