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)) )
--CREATE TABLE Diamond(ID INT , 证书号 VARCHAR(10), VIP FLOAT(2)) --INSERT INTO Diamond --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 diamond WHERE EXISTS ( SELECT 1 FROM ( SELECT MIN(id) id , 证书号 , MIN(vip) vip FROM diamond GROUP BY 证书号 ) b WHERE diamond.id <> b.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 10000 7.3
delete from tb where id not in (select id from tb tb1 where tb1.id =( select min(id) from tb tb2 where tb2.证书号=tb1.证书号 and tb2.vip<=tb1.vip))
CREATE TABLE Diamond(ID INT , 证书号 VARCHAR(10), VIP FLOAT(2)) INSERT INTO Diamond 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 Diamond WHERE ID IN( SELECT ID FROM Diamond a WHERE NOT EXISTS(SELECT 1 FROM Diamond b WHERE a.证书号=b.证书号 and a.VIP =b.VIP AND a.ID<b.ID ))--结果--- 1 10000 7.1
delete from dbo.Table_vip where zsh IN(select zsh from dbo.Table_vip group by zsh HAVING COUNT(zsh)>1) and vip not in(SELECT vip FROM Table_vip GROUP BY vip HAVING COUNT(vip) > 1) or ID in (SELECT max(id) FROM Table_vip)
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))
)
--INSERT INTO Diamond
--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 diamond
WHERE EXISTS ( SELECT 1
FROM ( SELECT MIN(id) id ,
证书号 ,
MIN(vip) vip
FROM diamond
GROUP BY 证书号
) b
WHERE diamond.id <> b.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 10000 7.3
select min(id) from tb tb2 where tb2.证书号=tb1.证书号 and tb2.vip<=tb1.vip))
CREATE TABLE Diamond(ID INT , 证书号 VARCHAR(10), VIP FLOAT(2))
INSERT INTO Diamond
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
Diamond WHERE ID IN(
SELECT ID FROM Diamond a WHERE NOT EXISTS(SELECT 1 FROM Diamond b WHERE a.证书号=b.证书号 and a.VIP =b.VIP AND a.ID<b.ID ))--结果---
1 10000 7.1
where zsh IN(select zsh from dbo.Table_vip
group by zsh HAVING COUNT(zsh)>1)
and vip not in(SELECT vip FROM Table_vip
GROUP BY vip HAVING COUNT(vip) > 1)
or ID in (SELECT max(id) FROM Table_vip)