表t：
id name isVip
1 jc 0
2 jc 0
3 jc 1
4 jc 0
5 zz 0
6 aa 1
7 aa 0
8 cc 1
原来我是这样删除重复的：
delete from t where id not in (select min(id) from t group by name)
随便保留一个数据就行。
后来发现在有重复数据的情况下，我需要优先保留一条isVip=1的数据，得到如下结果：
id name isVip
3 jc 1
5 zz 0
6 aa 1
8 cc 1
请各位高手指教啊。
------解决方案--------------------
- SQL code
--先删除下有vip=1的名称的vip=0的记录DELETE FROM t WHERE isvip=0 AND EXISTS(SELECT * FROM t AS t0 WHERE t0.NAME=t.NAME AND t0.isvip=1)--然后再执行你的 --原来我是这样删除重复的：delete from t where id not in (select min(id) from t group by name)
------解决方案--------------------
- SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN DROP TABLE tbaENDGOCREATE TABLE tba( id INT, name VARCHAR(100), isVip INT)GOINSERT INTO tbaSELECT 1, 'jc', 0 UNION ALLSELECT 2, 'jc', 0 UNION ALLSELECT 3, 'jc', 1 UNION ALLSELECT 4, 'jc', 0 UNION ALLSELECT 5, 'zz', 0 UNION ALLSELECT 6, 'aa', 1 UNION ALLSELECT 7, 'aa', 0 UNION ALLSELECT 8, 'cc', 1GOdelete from tba where id not in (select min(id) from tba AS A WHERE isVip = 1 OR NOT EXISTS (SELECT 1 FROM tba WHERE isVip = 1 AND A.name = name) group by name)SELECT * FROM tba
------解决方案--------------------
- SQL code
delete from tbawhere exists (select 1 from (select row_number() over(partition by [name] order by [isVip] desc) rn, * FROM tba) t where t.rn<>1 and tba.id=t.id)
------解决方案--------------------
- SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([id] INT,[name] VARCHAR(2),[isVip] INT)INSERT [tb]SELECT 1,'jc',0 UNION ALLSELECT 2,'jc',0 UNION ALLSELECT 3,'jc',1 UNION ALLSELECT 4,'jc',0 UNION ALLSELECT 5,'zz',0 UNION ALLSELECT 6,'aa',1 UNION ALLSELECT 7,'aa',0 UNION ALLSELECT 8,'cc',1--------------开始查询--------------------------DELETE a FROM [tb] AS aWHERE id !=(SELECT TOP 1 id FROM [tb] AS b WHERE b.[name]=a.[name] ORDER BY isVip DESC,[name])----------------结果----------------------------/* id name isVip----------- ---- -----------3 jc 15 zz 06 aa 18 cc 1(4 行受影响)*/