如何根据阈值删除除某些记录以外的所有记录？

Question

我有一个这样的表：

CREATE TABLE #TEMP(id int, name varchar(100))

INSERT INTO #TEMP VALUES(1, 'John')
INSERT INTO #TEMP VALUES(1, 'Adam')
INSERT INTO #TEMP VALUES(1, 'Robert')
INSERT INTO #TEMP VALUES(1, 'Copper')
INSERT INTO #TEMP VALUES(1, 'Jumbo')
INSERT INTO #TEMP VALUES(2, 'Jill')
INSERT INTO #TEMP VALUES(2, 'Rocky')
INSERT INTO #TEMP VALUES(2, 'Jack')
INSERT INTO #TEMP VALUES(2, 'Lisa')
INSERT INTO #TEMP VALUES(3, 'Amy')

SELECT *
FROM #TEMP


DROP TABLE #TEMP

我正在尝试删除具有3个以上具有相同ID的名称的记录，但要删除所有记录。因此，我试图得到这样的东西：

id  name
1   Adam
1   Copper
1   John
2   Jill
2   Jack
2   Lisa
3   Amy

我不了解如何编写此查询。我已经达到了保留一个记录但没有记录阈值的程度：

;WITH FILTER AS
(
    SELECT id 
    FROM #TEMP
    GROUP BY id
    HAVING COUNT(id) >=3 
)
SELECT id, MAX(name)
FROM #TEMP
WHERE id IN (SELECT * FROM FILTER)
GROUP BY id
UNION
SELECT id, name
FROM #TEMP
WHERE id NOT IN (SELECT * FROM FILTER)

给我：

1   Robert
2   Rocky
3   Amy

有什么建议？哦，对了，我不在乎合并时会保留哪些记录。

Answer 1

您可以使用CTE做到这一点

CREATE TABLE #TEMP(id int, name varchar(100))
INSERT INTO #TEMP VALUES(1, 'John')
INSERT INTO #TEMP VALUES(1, 'Adam')
INSERT INTO #TEMP VALUES(1, 'Robert')
INSERT INTO #TEMP VALUES(1, 'Copper')
INSERT INTO #TEMP VALUES(1, 'Jumbo')
INSERT INTO #TEMP VALUES(2, 'Jill')
INSERT INTO #TEMP VALUES(2, 'Rocky')
INSERT INTO #TEMP VALUES(2, 'Jack')
INSERT INTO #TEMP VALUES(2, 'Lisa')
INSERT INTO #TEMP VALUES(3, 'Amy')

SELECT *
FROM #TEMP;

WITH CTE(N) AS
(
 SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY id)
 FROM #Temp
)
DELETE CTE WHERE N>3;

SELECT *
FROM #TEMP;

DROP TABLE #TEMP