依据某个键值，删除后重置序号列_Sql Server

根据某个键值，删除后重置序号列
id pid name
1 1 www
1 2 fffff
1 3 aaa
1 4 cccc
2 1 vv
2 2 tttt

删除第2和第3列数据变为
id pid name
1 1 www
1 2 cccc
2 1 vv
2 2 tttt

请问这个用什么方式实现比较好

------解决方案--------------------

SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    ID INT,    PID INT,    name VARCHAR(100))GOCREATE TRIGGER TR_Tba_Delete ON tba FOR DELETEASBEGIN    DECLARE @Delete TABLE(TID INT IDENTITY(1,1),ID INT,PID INT,Name VARCHAR(100))    DECLARE @line INT    DECLARE @Total INT        SET @line = 1        INSERT INTO @Delete    SELECT ID,PID,Name    FROM deleted    ORDER BY ID,PID DESC        SELECT @Total = COUNT(1) FROM @Delete        WHILE @line <= @Total    BEGIN            UPDATE tba SET tba.PID = tba.PID - 1 FROM @Delete AS A WHERE a.ID = tba.ID AND a.PID < tba.PID AND a.TID = @line                SET @line = @line + 1        END        ENDGOINSERT INTO tbaSELECT 1, 1, 'www' UNIONSELECT 1, 2, 'fffff' UNIONSELECT 1, 3, 'aaa' UNIONSELECT 1, 4, 'cccc' UNIONSELECT 2, 1, 'vv' UNIONSELECT 2, 2, 'tttt'SELECT * FROM tbaID    PID    name1    1    www1    2    fffff1    3    aaa1    4    cccc2    1    vv2    2    ttttDELETE FROM tba WHERE name IN ('aaa','vv')SELECT * FROM tbaID    PID    name1    1    www1    2    fffff1    3    cccc2    1    tttt
------解决方案--------------------
SQL codeCREATE TABLE t1(    id INT,    pid INT,    name VARCHAR(10))INSERT INTO t1SELECT 1,    1,    'www' UNION ALLSELECT 1,    2,    'fffff' UNION ALLSELECT 1,    3,    'aaa' UNION ALLSELECT 1,    4,    'cccc' UNION ALLSELECT 2,    1,    'vv' UNION ALLSELECT 2,    2,    'tttt'SELECT * FROM t1DELETE FROM t1 WHERE id=1 AND pid IN (2,3)SELECT id,ROW_NUMBER() OVER(PARTITION BY id ORDER BY pid) AS pid,name  FROM t1------------------------id    pid    name1    1    www1    2    cccc2    1    vv2    2    tttt