根据条件，删除重复字段的记录？
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 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
/*
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_IDRC_DeviceIDRC_TimeNo
111
212
313
514
615
721
822
923
1024
*/
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
--> 测试数据：[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 all
select 2,1,2 union all
select 3,1,3 union all
select 4,1,3 union all
select 5,1,4 union all
select 6,1,4 union all
select 7,1,5 union all
select 8,2,1 union all
select 9,2,2 union all
select 10,2,3 union all
select 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 1
2 1 2
4 1 3
6 1 4
7 1 5
8 2 1
9 2 2
10 2 3
11 2 4
(9 行受影响)
*/
/*应该这样
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 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_IDRC_DeviceIDRC_TimeNo
111
212
413
614
715
821
922
1023
1124
*/
----------------------------
-- 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 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
--------------开始查询--------------------------
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 1
2 1 2
3 1 3
3 1 3
5 1 4
6 1 5
7 2 1
8 2 2
9 2 3
10 2 4
(10 行受影响)
*/
