sql-获取上一行并用条件复制它

t98cgbkg 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(318)

我有一张像下面这样的table

ID      status          message     set  
---------------------------------------
1       Completed       step1       1  
2       Completed       step2       1  
3       Failed          step3       1  
4       Completed       step1       2  
5       Completed       step2       2  
6       Failed          step3       2

对于每个集合，如果有一个失败的步骤，我需要复制上一个步骤并创建一个状态为“new”的新行。所以我的table需要看起来像

ID      status          message     set  
---------------------------------------
1       Completed       step1       1  
2       Completed       step2       1  
3       Failed          step3       1  
4       Completed       step1       2  
5       Completed       step2       2  
6       Failed          step3       2  
7       New             step2       1  
8       New             step2       2

有人能帮我做一个sql查询吗？我一直在尝试用lag和db\u cursor来写一些东西，但到目前为止还没有成功。我会继续努力，如果有人给我指出正确的方向，那会很有帮助。

mysql

来源：https://stackoverflow.com/questions/50134591/sql-get-previous-row-and-duplicate-it-with-a-condition

1条答案

按热度按时间

d8tt03nd1#

我得出了下面的解决方案。

DECLARE @set INT,
        @id  INT

--create a temporary table collecting the set value for the rows with failed message

IF OBJECT_ID(N'tempdb.dbo.#failList') IS NOT NULL
DROP TABLE #failList
SELECT  set
INTO    #failList
FROM    my_table WITH (NOLOCK)
WHERE  status = 'Failed'
and message = 'step3'

--loop over the failList and match the step to get the previous row and duplicate it

WHILE EXISTS (SELECT TOP 1 1 FROM #failList)
BEGIN
SELECT TOP 1 @step = step
FROM    #failList

INSERT  INTO my_table (status, message, set)
SELECT  'New', message, set]
FROM    my_table d WITH (NOLOCK)
    WHERE   d.set = @set
    and message = 'step2'

DELETE FROM #failList WHERE set = @set      
END

赞(0）回复(0）举报 2021-06-21

我来回答

sql-获取上一行并用条件复制它

1条答案

相关问题

热门标签

最新问答