根据条件，删除重复字段的记录？该怎么处理

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 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 tb

truncate table tb

insert tb 
select RC_ID,  RC_DeviceID , RC_TimeNo from #t t
where not exists(select * from #t where RC_DeviceID =t.RC_DeviceID and RC_TimeNo=t.RC_TimeNo and id>t.id)


select * from tb
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

(9 行受影响)


drop table #t

------解决方案--------------------

SQL code

/*
Micsosoft Windows 7.0 7600
Microsoft 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 tb
go
CREATE TABLE tb(RC_ID  int,  RC_DeviceID  int,  RC_TimeNo  int)
INSERT tb SELECT
1   ,     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_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
*/

------解决方案--------------------

SQL code

declare @TB table
(
  RC_ID int,
  RC_DeviceID int,
  RC_TimeNo int
)
insert into @TB select 1,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,5
union 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           1
2           1           2           2
3           1           3           3
4           1           4           5
5           1           5           6
6           1           5           7