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
...
------解决方案--------------------
- SQL code
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
------解决方案--------------------
- SQL code
/*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*/
------解决方案--------------------
- SQL code
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