日期:2014-05-17 浏览次数:20583 次
DELETE dbo.Diamond
WHERE 证书号 IN ( SELECT 证书号
FROM Diamond
GROUP BY 证书号
HAVING COUNT(证书号) > 1 )
AND VIP价 NOT IN ( SELECT MIN(VIP价)
FROM Diamond
GROUP BY 证书号
HAVING COUNT(证书号) > 1 )
delete Diamond from Diamond a
where exists
(
select 1 from Diamond b
where a.证书号=b.证书号
and (b.vip<a.vip or (b.vip=a.vip and b.id<a.id))
)
IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA( ID INT,Number VARCHAR(10),VIP FLOAT)
INSERT INTO TA
SELECT 1,'10000',7.1 UNION ALL
SELECT 2,'10000',7.2 UNION ALL
SELECT 3,'10000',7.3 UNION ALL
SELECT 4,'10000',7.1
--删除语句
DELETE FROM TA
WHERE EXISTS(
SELECT ID, Number,VIP FROM TA AS A
WHERE TA.Number=A.Number AND TA.VIP=A.VIP AND TA.ID>A.ID
)
--查看结果
SELECT * FROM TA
ID Number VIP
----------- ---------- ----------------------
1 10000 7.1
2 10000 7.2
3 &nb