34,590
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a TABLE(id INT,c1 VARCHAR(10))
INSERT INTO @a SELECT 1,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 4,'b' UNION ALL
SELECT 5,'b' UNION ALL
SELECT 6,'c' UNION ALL
SELECT 7,'c';
DECLARE @b TABLE(fid int)
INSERT INTO @b(fid) VALUES(2),(4),(5);
DELETE FROM @a WHERE id NOT IN (
SELECT MIN(id) AS id FROM @a WHERE id IN (SELECT fid FROM @b)
GROUP BY c1
UNION ALL
SELECT MIN(id) FROM @a a WHERE NOT EXISTS(SELECT 1 FROM @a x WHERE a.c1=x.c1 AND x.id IN (SELECT fid FROM @b))
GROUP BY a.c1
);
SELECT * FROM @a;
/*
id c1
----------- ----------
2 a
4 b
6 c
*/
begin tran
delete a
where id not in (select id from
(select a.id,b.fid as id1 , name,a.address ,ROW_NUMBER()over(PARTITION by name,address order by
b.fid desc ,a.id desc ) as n from a left join b on a.id=b.fid) as t
where n=1 )
select * from a
id name address
----------- ---- -------
2 zs aaa
4 sm bbb
6 lr ccc
7 ms ddd
8 xd eee
(5 行受影响)
--结果