贴一个垃圾的,请赐教!USE master
CREATE TABLE TEST1
(
[ID] INT NOT NULL,
[Name] NVARCHAR(16) NOT NULL,
[DETAIL] NVARCHAR(128)
)INSERT INTO TEST1 VALUES(1,'非重复1','this is distinct row')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(3,'非重复2','this is distinct row')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(5,'非重复3','this is distinct row')
INSERT INTO TEST1 VALUES(6,'非重复4','this is distinct row')SELECT DISTINCT * FROM TEST1
SELECT * FROM TEST1DECLARE @ROWNUM INT
SELECT @ROWNUM=COUNT(*) FROM TEST1
PRINT(@ROWNUM)INSERT INTO TEST1 SELECT DISTINCT * FROM TEST1
SET ROWCOUNT @ROWNUM
DELETE FROM TEST1SELECT * FROM TEST1DROP TABLE TEST1
CREATE TABLE TEST1
(
[ID] INT NOT NULL,
[Name] NVARCHAR(16) NOT NULL,
[DETAIL] NVARCHAR(128)
)INSERT INTO TEST1 VALUES(1,'非重复1','this is distinct row')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(3,'非重复2','this is distinct row')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(5,'非重复3','this is distinct row')
INSERT INTO TEST1 VALUES(6,'非重复4','this is distinct row')SELECT DISTINCT * FROM TEST1
SELECT * FROM TEST1DECLARE @ROWNUM INT
SELECT @ROWNUM=COUNT(*) FROM TEST1
PRINT(@ROWNUM)INSERT INTO TEST1 SELECT DISTINCT * FROM TEST1
SET ROWCOUNT @ROWNUM
DELETE FROM TEST1SELECT * FROM TEST1DROP TABLE TEST1
(
[ID] INT NOT NULL,
[Name] NVARCHAR(16) NOT NULL,
[DETAIL] NVARCHAR(128)
)INSERT INTO TEST1 VALUES(1,'非重复1','this is distinct row')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(3,'非重复2','this is distinct row')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(5,'非重复3','this is distinct row')
INSERT INTO TEST1 VALUES(6,'非重复4','this is distinct row')select distinct * into #tmp from TEST1truncate table TEST1insert TEST1 select * from #tmpselect * from TEST1
set xact_abort on
begin tran
select distinct * into #tmp from TEST1
truncate table TEST1
insert TEST1 select * from #tmp
select * from TEST1
commit tran
set xact_abort off
goCREATE TABLE TEST1
(
[ID] INT NOT NULL,
[Name] NVARCHAR(16) NOT NULL,
[DETAIL] NVARCHAR(128)
)INSERT INTO TEST1 VALUES(1,'非重复1','this is distinct row')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(3,'非重复2','this is distinct row')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(5,'非重复3','this is distinct row')
INSERT INTO TEST1 VALUES(6,'非重复4','this is distinct row')
go
--drop table TEST1declare T_cursor cursor local for
SELECT ID,COUNT(*)-1 FROM TEST1 group by ID having COUNT(*)>1
DECLARE @ROWNUM INT,@ID int
open T_cursor
fetch next from T_cursor into @ID,@ROWNUM
while @@fetch_status=0
begin
SET ROWCOUNT @ROWNUM
DELETE TEST1 where ID=@ID
fetch next from T_cursor into @ID,@ROWNUM
SET ROWCOUNT 0
end
close T_cursor
deallocate T_cursorgo
SELECT * FROM TEST1
select * from TEST1 where name not in (select name from TEST1 group by name having count(*)>1)
from (select row_number() over(partition by id order by id)rowid,* from test1 )b
where b.rowid <>1
delete test1 where detail='delete标记'
如果所有字段重复
--- use pubs
select * into ppp from authors --备份表
truncate table authors --清空表
insert authors
select distinct * from pppp ---把过滤掉的数据插入进去如果是个别字段重复
----------- delete authors where au_id in (select au_id from authors group by au_id having count(*)>1)
CREATE TABLE TEST1
(
[ID] INT NOT NULL,
[Name] NVARCHAR(16) NOT NULL,
[DETAIL] NVARCHAR(128)
)INSERT INTO TEST1 VALUES(1,'非重复1','this is distinct row')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(2,'重复1','')
INSERT INTO TEST1 VALUES(3,'非重复2','this is distinct row')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(4,'重复2','')
INSERT INTO TEST1 VALUES(5,'非重复3','this is distinct row')
INSERT INTO TEST1 VALUES(6,'非重复4','this is distinct row')
delete from test1 where id not in(
select id from test1 A where 2>( select count(1) from test1 where id=A.id and name=A.Name and Detail =A.detail))
select * from test11 非重复1 this is distinct row
3 非重复2 this is distinct row
5 非重复3 this is distinct row
6 非重复4 this is distinct row
delete cte where rn>1
(
[ID] INT NOT NULL,
[Name] NVARCHAR(16) NOT NULL,
[DETAIL] NVARCHAR(128)
)INSERT INTO #TEST1 VALUES(1,'非重复1','this is distinct row')
INSERT INTO #TEST1 VALUES(2,'重复1','')
INSERT INTO #TEST1 VALUES(2,'重复1','')
INSERT INTO #TEST1 VALUES(2,'重复1','')
INSERT INTO #TEST1 VALUES(3,'非重复2','this is distinct row')
INSERT INTO #TEST1 VALUES(4,'重复2','')
INSERT INTO #TEST1 VALUES(4,'重复2','')
INSERT INTO #TEST1 VALUES(4,'重复2','')
INSERT INTO #TEST1 VALUES(4,'重复2','')
INSERT INTO #TEST1 VALUES(5,'非重复3','this is distinct row')
INSERT INTO #TEST1 VALUES(6,'非重复4','this is distinct row')--添加唯一性标志列
alter table #test1 add distinctid int identity(1,1)
delete from #test1 where distinctid not in
(
select max(distinctid) from #test1
group by id,name,detail
)
alter table #test1 drop column distinctid
--恢复表结构
select * from #test1