34,575
社区成员
发帖
与我相关
我的任务
分享
--生成新表
SELECT DISTINCT * INTO NewTableName FROM TableName
--删除旧表
DROP TABLE TableName
--重命名表
EXEC sys.sp_rename @objname = N'NewTableName', -- nvarchar(1035)
@newname = 'TableName' -- sysname
DELETE t FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY test1,test2,test3,test4 ORDER BY RAND()) AS RN FROM TableName) AS t WHERE RN>1
生成伪列去删除
-- partition by 后面不能写 text varbinary 这些数据类型
create table test(a int, b int, c int, d int)
go
insert into test values
(1, 2, 3, 4),
(1, 2, 3, 4),
(5, 6, 7, 8),
(5, 6, 7, 8)
go
with m as (
select
*,
row_number() over(partition by a,b,c,d order by a) rn
from test
)
delete m where rn > 1
go
select * from test
go
drop table test
go
(2 行受影响)
a b c d
----------- ----------- ----------- -----------
1 2 3 4
5 6 7 8
(2 行受影响)