如何根据阈值删除除部分记录外的所有记录

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

Answer 2

我将像这样改变你的选择(未测试)

select name from #temp group by name having count(id) > 3

，那么你可以在delete语句中使用select作为where子句

Answer 3

在内部查询中可以使用row_number函数over (partition by id)然后在外部查询中你需要给出条件，比如

select id,name from (
SELECT id,name, row_number() over (partition by id order by 1) count_id FROM #test
group by id, name ) 
where count_id <=3

Answer 4

如果我答对了你的问题，你需要得到id出现3次或更多次的行

select t1.name,t1.id from tbl1 t1 
inner join tbl1 t2 on t1.id = t2.id
group by t1.name, t1.id
having count(t1.id) > 2