删除完全一样的重复数据, 保留其中一条

[sql]
--1. 删除已存在表.    
IF EXISTS (    
       SELECT 1 FROM sysobjects  WHERE  id = OBJECT_ID(N'test') AND OBJECTPROPERTY(id, N'IsUserTable') = 1    
   )    
BEGIN    
    DROP TABLE test    
END    
GO

--2. 建表

CREATE TABLE [dbo].test( [userId] [int] NOT NULL, [name] VARCHAR(20) NOT NULL,isEnabled BIT)  
GO  
  
INSERT INTO dbo.test   
SELECT 1,'aa',0 UNION ALL --注：只能用 union, 不能用 union all. 要不只会有一条记录  
SELECT 1,'aa',1 UNION ALL  
SELECT 2,'bb',0 UNION ALL  
SELECT 2,'bb',0 UNION ALL  
SELECT 2,'cc',1 UNION ALL  
SELECT 3,'dd',0  
GO  
  
--如果有视图先删除  
IF EXISTS(SELECT 1 FROM sys.views WHERE name='tmp_view_test')  
    DROP VIEW tmp_view_test  
GO  
--创建视图 (注：PARTITION BY的功能为以xxx列来分组， 有点相当于group by)  
--尽可能删除 isEnabled = 0 的记录  
CREATE VIEW tmp_view_test AS  
SELECT ROW_NUMBER() OVER(PARTITION BY [userId] ORDER BY ISNULL(isEnabled,0) DESC) AS num,*  
FROM test  
GO

--删除前情况  
SELECT * FROM tmp_view_test ORDER BY userid,num  
  
--删除重复  
DELETE FROM tmp_view_test WHERE num!=1  
  
--删除后情况  
SELECT * FROM tmp_view_test ORDER BY userid,num