怎么删除完全重复的数据啊

[qq_40656191]

用distinct *from 表 行不通，有别的方法吗

[日月路明]

所以说，主键是必须的，否则像这种情况就很麻烦，虽然可以解决，但毕竟很麻烦

[砸死牛顿的苹果]

引用3楼数据,直接派生表进行delete 也可以

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(
n NVARCHAR(20)   
)
INSERT INTO t (n) VALUES ('1')
INSERT INTO t (n) VALUES ('1')
INSERT INTO t (n) VALUES ('2')
INSERT INTO t (n) VALUES ('2')
INSERT INTO t (n) VALUES ('3')

DELETE T 
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY n ORDER BY (select 1)) AS rid,* FROM t)T
WHERE rid>1
 
SELECT * FROM t

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] int,[b] int,[c] int)
Insert #T
select 1,1,1 union all
select 2,2,2 union all
select 1,1,1 union all
select 3,3,1 union all
select 1,1,2
Go
--测试数据结束
DELETE  a
FROM    #T a
        JOIN ( SELECT   a ,
                        b ,
                        c
               FROM     #T
               GROUP BY a ,
                        b ,
                        c
               HAVING   COUNT(1) > 1
             ) t ON t.a = a.a
                    AND t.b = a.b
                    AND t.c = a.c
SELECT * FROM #T

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(
id INT IDENTITY(1,1) PRIMARY KEY,
n NVARCHAR(20)	
)
INSERT INTO t (n) VALUES ('1')
INSERT INTO t (n) VALUES ('1')
INSERT INTO t (n) VALUES ('2')
INSERT INTO t (n) VALUES ('2')
INSERT INTO t (n) VALUES ('3')

;WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY n ORDER BY id ASC) AS rid,* FROM t	
)
DELETE FROM cte 
WHERE rid!=1

SELECT * FROM t
/*
id          n
----------- --------------------
1           1
3           2
5           3
*/

[吉普赛的歌]

哦， 你要的是完全重复， 也差不多吧：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t(
n NVARCHAR(20)	
)
INSERT INTO t (n) VALUES ('1')
INSERT INTO t (n) VALUES ('1')
INSERT INTO t (n) VALUES ('2')
INSERT INTO t (n) VALUES ('2')
INSERT INTO t (n) VALUES ('3')

;WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY n ORDER BY (select 1)) AS rid,* FROM t	
)
DELETE FROM cte 
WHERE rid!=1

SELECT * FROM t
/*
n
--------------------
1
2
3
*/

[繁花尽流年]

3楼那个就可以，逻辑就是完全一样的数据给他个自增序号，然后保留序号1的记录就行了。

[日月路明]

有一个终极解决方法，可以适应所有表的处理，大体上是这样 select distinct * into TempTable from Tablea delete from Tablea 这个有一个小缺陷，如股票有自增字段，需要这样写 set identity_insert Tablea on insert into tablea select * from TempTable set identity_insert Tablea off

[砸死牛顿的苹果]

楼上这是最早的版本的写法 而且效率稍微低了一些~