我目前有這樣的資料庫,如下:(裡面資料表如下:)A001 廣東 WWW
A002 廣東 SSS
A003 陝西 111
A005 北京 ooo
A006 陝西 qqq
.............
我現在的要求如下:(我要把中間的重復記錄刪除掉,不過不能全部刪除,至少要保留一條,隨便那條記錄都可以!)
A001 廣東 WWW
A005 北京 ooo
A006 陝西 qqq
A002 廣東 SSS
A003 陝西 111
A005 北京 ooo
A006 陝西 qqq
.............
我現在的要求如下:(我要把中間的重復記錄刪除掉,不過不能全部刪除,至少要保留一條,隨便那條記錄都可以!)
A001 廣東 WWW
A005 北京 ooo
A006 陝西 qqq
--假設三個字段為 COL1, COL2 ,COL3
Delete A From 表 A Where Exists(Select COL1 From 表 Where COL2 =A.COL2 And COL1 < A.COL1)
--假設三個字段為 COL1, COL2 ,COL3
Delete A From 表 A Where COL1 Not In(Select Min(COL1) From 表 Where COL2 =A.COL2)
INSERT INTO @t SELECT 'A001','廣東','WWW'
UNION ALL SELECT 'A002','廣東','SSS'
UNION ALL SELECT 'A003','陝西','111'
UNION ALL SELECT 'A005','北京','ooo'
UNION ALL SELECT 'A006','陝西','qqq'DELETE FROM @t WHERE col1 NOT IN (SELECT MAX(col1) FROM @t GROUP BY col2)
SELECT * FROM @t
---------- -------------------- ----------
A002 廣東 SSS
A005 北京 ooo
A006 陝西 qqq
insert @t
select 'A001', '廣東', 'WWW' union all
select 'A002', '廣東', 'SSS' union all
select 'A003', '陝西', '111' union all
select 'A005', '北京', 'ooo' union all
select 'A006', '陝西', 'qqq'----保留id最大的
delete a from @t as a where
exists(select 1 from @t where name = a.name and id > a.id)
----保留id最小的
delete a from @t as a where
exists(select 1 from @t where name = a.name and id < a.id)
----查看
select * from @t/*结果
id name x
---------- ---------- ----------
A002 廣東 SSS
A005 北京 ooo
A006 陝西 qqq
*/
insert @software51
select 'A001', '廣東', 'WWW' union all
select 'A002', '廣東', 'SSS' union all
select 'A003', '陝西', '111' union all
select 'A005', '北京', 'ooo' union all
select 'A006', '陝西', 'qqq'delete @software51 where id not in(select max(id) from @software51 group by name)select * from @software51
/*结果
id name x
---------- ---------- ----------
A002 廣東 SSS
A005 北京 ooo
A006 陝西 qqq
*/
INSERT INTO @t SELECT 'A001','廣東','WWW'
UNION ALL SELECT 'A002','廣東','SSS'
UNION ALL SELECT 'A003','陝西','111'
UNION ALL SELECT 'A005','北京','ooo'
UNION ALL SELECT 'A006','陝西','qqq'Delete A From @t A Where Exists(Select COL1 From @t Where COL2 =A.COL2 And COL1 < A.COL1)
SELECT * FROM @t--Result
/*
col1 col2 col3
A001 廣東 WWW
A003 陝西 111
A005 北京 ooo
*/
DECLARE @t TABLE(col1 VARCHAR(10), col2 VARCHAR(20),col3 VARCHAR(10))
INSERT INTO @t SELECT 'A001','廣東','WWW'
UNION ALL SELECT 'A002','廣東','SSS'
UNION ALL SELECT 'A003','陝西','111'
UNION ALL SELECT 'A005','北京','ooo'
UNION ALL SELECT 'A006','陝西','qqq'Delete A From @t A Where COL1 Not In(Select Min(COL1) From @t Where COL2 =A.COL2)
SELECT * FROM @t--Result
/*
col1 col2 col3
A001 廣東 WWW
A003 陝西 111
A005 北京 ooo
*/