34,576
社区成员
发帖
与我相关
我的任务
分享
;with cte as(
select name,no,item,row_number() over(partition by name,no order by item ) as cnt from #tab
)
delete from #tab where exists(select 'x' from cte where cte.name=#tab.name and cte.no=#tab.no and cte.item=#tab.item and cte.cnt>=2)
CREATE TABLE #tab([NAME] VARCHAR(10),[no] VARCHAR(10),item INT)
INSERT #tab
SELECT 'a','001',2 UNION ALL
SELECT 'a','002',4 UNION ALL
SELECT 'b','003',4 UNION ALL
SELECT 'a','001',9 UNION ALL
SELECT 'b','003',8
SELECT a.* from #tab a,(SELECT NAME,no FROM #tab t
GROUP BY NAME,no HAVING COUNT(*)>1)b WHERE a.[NAME]=b.[NAME] AND a.no=b.no
/*
NAME no item
---------- ---------- -----------
a 001 2
a 001 9
b 003 4
b 003 8
(4 行受影响)
*/
select * from vitae a
where exists(select 1 from vitae where a.peopleId=peopleId and a.seq=seq)