基于其他列值重复行

Question

我正在从表中提取订单，每个订单都有一个状态和交货时间。订单以确切顺序从状态中通过如下："放置">"已确认">"已发货"

行看起来像

id_order    dsc_status  lead
1           placed      8
1           confirmed   5
1           shipped     1

我需要返回：

id_order    dsc_status  lead     iter
    1           placed      8    0
    1           placed      8    1
    1           placed      8    2
    1           placed      8    3
    1           confirmed   5    0
    1           confirmed   5    1
    1           confirmed   5    2
    1           confirmed   5    3
    1           confirmed   5    4
    1           shipped     1    0
    1           shipped     1    1

逻辑：i示例，放置和确认之间的提前时间差为3，因此我重复了4次(基于0的计数(，以进行确认>发货。对于发货，我们重复一遍，好像是铅= 0的虚拟状态，这意味着我们重复2次，请检查结果。

Answer 1

您可以使用光标获得所需的输出 -

DECLARE @id_order INT
DECLARE @id_Status_order INT
DECLARE @dsc_statue VARCHAR(100)
DECLARE @lead INT
DECLARE @LoopCount INT
DECLARE @TmpTable TABLE
(
    id_order INT,    dsc_status VARCHAR(200),  lead INT,     iter INT
)
DECLARE @id_order_prev INT
DECLARE @dsc_statue_prev VARCHAR(100)
DECLARE @lead_prev INT
DECLARE db_cursor CURSOR FOR 
SELECT id_order,Status_Order,dsc_status,lead
FROM 
(
    SELECT id_order,dsc_status,lead, 
    CASE 
        WHEN dsc_status = 'placed' THEN 1 
        WHEN dsc_status = 'confirmed' THEN 2
        WHEN dsc_status = 'shipped' THEN 3
    END Status_Order
    FROM your_table
)A
ORDER BY 1,2
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @id_order,@id_Status_order ,@dsc_statue,@lead
WHILE @@FETCH_STATUS = 0  
BEGIN
    IF @id_order_prev IS NULL
    BEGIN
        SET @id_order_prev = @id_order
        SET @dsc_statue_prev = @dsc_statue
        SET @lead_prev = @lead
    END
    ELSE
    BEGIN
        SET @LoopCount  = 0 
        WHILE @LoopCount <= CASE WHEN @id_order_prev = @id_order THEN ABS(@lead_prev-@lead) ELSE @lead_prev END
        BEGIN
           INSERT INTO @TmpTable (id_order,dsc_status,lead,iter)
           VALUES (@id_order_prev,@dsc_statue_prev,@lead_prev,@LoopCount)
           SET @LoopCount = @LoopCount + 1
        END
        SET @id_order_prev = @id_order
        SET @dsc_statue_prev = @dsc_statue
        SET @lead_prev = @lead
    END

    FETCH NEXT FROM db_cursor INTO @id_order,@id_Status_order ,@dsc_statue,@lead
    IF  @@FETCH_STATUS <> 0
    BEGIN 
        SET @LoopCount = 0
        WHILE @LoopCount <= @lead
        BEGIN
           INSERT INTO @TmpTable (id_order,dsc_status,lead,iter)
           VALUES (@id_order_prev,@dsc_statue_prev,@lead_prev,@LoopCount)
           SET @LoopCount = @LoopCount + 1
        END
    END
END 

CLOSE db_cursor  
DEALLOCATE db_cursor 

SELECT * 
FROM @TmpTable
ORDER BY 1