识别一个值不同的组中的重复项

Question

USE TEMPDB
go

IF OBJECT_ID(N'#TEST', N'U') IS NOT NULL
    DROP TABLE dbo.#TEST


CREATE TABLE #TEST
(NAME VARCHAR(50),
line int,
RANKS INT)
INSERT INTO #TEST
(name, line,RANKS)
VALUES
('Tom',1, 1), --keep
('Tom',2, 1),--keep
('Toms',1, 0), --keep
('Toms',2, 0),--keep
('Dave',1, 0),--delete
('Dave',2, 0),--keep
('Dave',1, 1),--keep
('TIm',1,1),--keep
('TIm',1,0),--delete
('Matt',1,0),--delete
('Matt',1,1)--keep

• 如果同名、同线属于不同的职级，我便要删除“0”级以下的“探子”，如果同一条线的同一人属于不同的职级，则应删除0级以下的“探子”。

Answer 1

像这样的事情应该有效：

delete t1
  from #TEST t1
 where exists(select *
                from #TEST t2
               where t2.NAME = t1.NAME
                 and t2.line = t1.line
                 and t2.RANKS <> t1.RANKS)
   and t1.RANKS = 0;