sql-server - 基于其他列值的重复行
问题描述
我正在从表中提取订单,每个订单都有状态和交货时间。订单从状态传递的确切顺序如下:“已放置”>“已确认”>“已发货”
行看起来像
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
逻辑:在示例中,放置和确认之间的提前期差异为 3,因此我重复放置行 4 次(基于 0 的计数),确认 > 发货时相同。对于已发货,我们会重复,就像后面跟着一个有铅 = 0 的虚构状态,这意味着我们重复 2 次,请检查结果。
解决方案
您可以使用 CURSOR 来获得所需的输出 -
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
推荐阅读
- html - 单向绑定无响应功能
- javascript - 使用 Vue-i18n 翻译来自脚本的文本
- symfony - 在没有 /usr/local/bin/composer 的服务器上使用 EasyDeployBundle 托管 Symfony 4 应用程序
- jquery - 当我添加溢出时,fancybox 正在跳转我的内容:隐藏到页面上只能看到 6 个图像
- qt - Qt 应用程序无法正确启动(0xc000007b)
- android - 如何在 Nativescript 中使用外部 sdk
- java - 用于复合 API 调用的 Spring Cloud Gateway?
- gwt - 隐藏
- c# - 在C#中引用目录时如何指定用户名
- java - 如何在室内导航中在定制地图上显示用户位置?