您可以使用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
