--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
不用视图删除:
--1. 删除已存在表.
IF OBJECT_ID(N'test') IS NOT NULL
BEGIN
DROP TABLE test
END
GO
--2. 建表
CREATE TABLE [dbo].test( [userId] [int] NOT NULL, [name] VARCHAR(20) NOT NULL,isEnabled BIT)
GO
SET NOCOUNT ON
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
SELECT * FROM test
/*
userId name isEnabled
1 aa 0
1 aa 1
2 bb 0
2 bb 0
2 cc 1
3 dd 0
*/
DELETE FROM tmp FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY userId ORDER BY isEnabled DESC) AS rid,* FROM test) AS tmp
WHERE rid!=1
SELECT * FROM test
/*
userId name isEnabled
1 aa 1
2 cc 1
3 dd 0
*/