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

根据某个键值，删除后重置序号列
id pid name
11www
12fffff
13aaa
14cccc
21vv
22tttt

删除 第2和第3列 数据变为
id pid name
11www
12cccc
21vv
22tttt


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

[解决办法]

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 code
CREATE 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