求sql语句，如何有条件的删除重复数据？

[topso]

表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

请各位高手指教啊。

[孤独加百列]

delete from t where id not in (select min(id) from t WHERE isVip = 1 group by name)

[叶子]

根据实际情况，选择适当的方式即可，参考：
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html

[zjl8008]

--先删除下有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)

[孤独加百列]

[Quote=引用 1 楼 的回复:]

SQL code

delete from t where id not in (select min(id) from t WHERE isVip = 1 group by name)
[/Quote]
少考虑个只有0的情况，等等

[topso]

[Quote=引用 1 楼 的回复:]
SQL code


delete from t where id not in (select min(id) from t WHERE isVip = 1 group by name)
[/Quote]

你这样得到的是下面的数据啊，id=5的数据没有保留下来呢。

id name isVip
3 jc 1
6 aa 1
8 cc 1

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
	DROP TABLE tba
END
GO
CREATE TABLE tba
(
	id INT,
	name VARCHAR(100),
	isVip INT
)
GO
INSERT INTO tba
SELECT 1,	'jc',	0 UNION ALL
SELECT 2,	'jc',	0 UNION ALL
SELECT 3,	'jc',	1 UNION ALL
SELECT 4,	'jc',	0 UNION ALL
SELECT 5,	'zz',	0 UNION ALL
SELECT 6,	'aa',	1 UNION ALL
SELECT 7,	'aa',	0 UNION ALL
SELECT 8,	'cc',	1
GO

delete 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

[问苍天人生几何]

delete from tba
where 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)

[wyy12802]

是保留一条0的数据,一条1的数据么?

[筱筱澄]

--> 测试数据：[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 ALL
SELECT 2,'jc',0 UNION ALL
SELECT 3,'jc',1 UNION ALL
SELECT 4,'jc',0 UNION ALL
SELECT 5,'zz',0 UNION ALL
SELECT 6,'aa',1 UNION ALL
SELECT 7,'aa',0 UNION ALL
SELECT 8,'cc',1

--------------开始查询--------------------------

DELETE  a FROM [tb] AS a
WHERE id !=(SELECT TOP 1 id  FROM [tb] AS b WHERE b.[name]=a.[name] ORDER BY isVip DESC,[name]
)

----------------结果----------------------------
/* 
id          name isVip
----------- ---- -----------
3           jc   1
5           zz   0
6           aa   1
8           cc   1

(4 行受影响)
*/

[topso]

感谢magician547、iamaitman、筱筱澄
都可以实现，magician547的效率最好，iamaitman还不支持sql2000，筱筱澄的易懂，但是执行效率不高，老是超时。

呵呵，谢谢你们了。