日期:2014-05-17  浏览次数:20585 次

根据某个键值,删除后重置序号列
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 tba
END
GO
CREATE TABLE tba
(
    ID INT,
    PID INT,
    name VARCHAR(100)
)
GO

CREATE TRIGGER TR_Tba_Delete ON tba FOR DELETE
AS
BEGIN

    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
    
    

END
GO



INSERT INTO tba
SELECT 1, 1, 'www' UNION
SELECT 1, 2, 'fffff' UNION
SELECT 1, 3, 'aaa' UNION
SELECT 1, 4, 'cccc' UNION
SELECT 2, 1, 'vv' UNION
SELECT 2, 2, 'tttt'

SELECT * FROM tba
ID    PID    name
1    1    www
1    2    fffff
1    3    aaa
1    4    cccc
2    1    vv
2    2    tttt

DELETE FROM tba WHERE name IN ('aaa','vv')

SELECT * FROM tba
ID    PID    name
1    1    www
1    2    fffff
1    3    cccc
2    1    tttt

------解决方案--------------------
SQL code

CREATE TABLE t1
(
    id INT,
    pid INT,
    name VARCHAR(10)
)
INSERT INTO t1
SELECT 1,    1,    'www' UNION ALL
SELECT 1,    2,    'fffff' UNION ALL
SELECT 1,    3,    'aaa' UNION ALL
SELECT 1,    4,    'cccc' UNION ALL
SELECT 2,    1,    'vv' UNION ALL
SELECT 2,    2,    'tttt'
SELECT * FROM t1

DELETE 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    name
1    1    www
1    2    cccc
2    1    vv
2    2    tttt