sql - 如何使用 Sql server 中另一个表中的值的条件更新表中的值
问题描述
我的项目中有 2 个表,WorkOrders 和 WorkOrderTrackings 并且 WorkOrderTrackings 有一个外键 WorkOrderId,它引用了 WorkOrders 中的 Id(因此您知道跟踪记录属于哪个工作订单)。WorkOrderTrackings 中的记录表示工作订单从开始到结束所经历的“步骤”。一旦我们的工程部门将工作订单发布到车间进行建设,它们就会创建。因此,任何工作订单都可以有 1 到多个工作订单跟踪。
现在,WorkOrderTrackings 有一个 StageNum 字段,它按照从开始到结束的顺序说明步骤的步骤#。有日期时间吗?开始日期和日期时间?CompleteDate 字段。当他们开始一个步骤时,开始日期被设置,当他们完成时,完成日期被设置。当它已经完成了 1 步并且还没有开始下一个步骤时,前一步的 completedate 有一个值,但下一步 startdate 没有。
所以目前,要确定工单 X 所处的步骤,我们使用 linq 表达式:
var trackingsForX=db.WorkOrderTrackings.Where(wot=>wot.WorkOrderId==X.Id);
var currentTrackingForX=trackingsForX.OrderByDescending(t=>t.StageNum)
.Where(t=>t.StartDate.HasValue && !t.CompleteDate.HasValue)
.FirstOrDefault() ??
trackingsForX.OrderBy(t=>t.StageName)
.Where(t=>!t.CompleteDate.HasValue)
.FirstOrDefault()
这会通过在搜索从 last 到 first stagenum 的跟踪时返回找到的第一个带有 startdate 值但没有完成的跟踪记录来选择工作订单的最近跟踪步骤,或者,如果返回 null,则通过查找没有完成的第一个记录通过从第一步到最后一步搜索来获得价值。
我们不想每次都这样做,所以我们向 WorkOrders 添加了一个字段 CurrentTrackingStepId,它是工作订单最新跟踪记录的外键。随着新步骤的开始,我们将更新工作订单的 CurrentTrackingStepId 以指向该步骤。这样,我们总是可以通过查找工单来访问工单的当前步骤。但是,现在我需要创建一个我们可以运行的 sql 脚本,它将自动将所有现有工作订单的 CurrentTrackingStepId 设置为该订单的当前跟踪 ID。
我将如何编写一个 sql 脚本来完成这个?
编辑:使用示例数据库数据更新
dbo.WorkOrderTrackings:
ID WO-Id StageNum StartDate CompleteDate
551874 53781 1 2020-08-28 5:13:34 PM 2020-08-28 5:13:45 PM
551875 53781 2 2020-08-28 7:01:35 PM 2020-08-28 7:01:35 PM
551878 53781 5 2020-08-28 7:01:35 PM NULL
551879 53781 6 NULL NULL
551880 53781 7 NULL NULL
551881 53781 8 NULL NULL
551882 53781 9 NULL NULL
551883 53781 10 NULL NULL
551884 53781 11 NULL NULL
551885 53781 12 NULL NULL
551886 53781 13 NULL NULL
551887 53781 14 NULL NULL
551888 53781 15 NULL NULL
551889 53781 16 NULL NULL
551890 53781 17 NULL NULL
551891 53803 1 2020-08-28 6:33:18 PM 2020-08-28 6:33:18 PM
551892 53803 2 2020-08-28 6:33:18 PM 2020-08-31 8:07:09 AM
551893 53803 3 2020-08-31 8:07:17 AM 2020-08-31 8:07:24 AM
551894 53803 4 NULL NULL
551895 53803 5 NULL NULL
551896 53803 6 NULL NULL
551897 53803 7 NULL NULL
551908 53803 18 NULL NULL
551909 53803 19 NULL NULL
551910 53803 20 NULL NULL
551911 53803 21 NULL NULL
551912 53803 22 NULL NULL
551913 53803 23 NULL NULL
551914 53803 24 NULL NULL
551915 53803 25 NULL NULL
551916 53803 26 NULL NULL
dbo.WorkOrders:
Id OrderNumber
53803 139107-00
53781 139094-00
因此,如果您查看上面的数据,如果我想要订单 139107-00 的当前跟踪步骤,它应该返回 ID 为 551894 的 WorkOrderTracking 记录(因为这是最近完成的记录之后的记录)。如果我想要订单 139094-00 的当前跟踪步骤,它应该返回 ID 为 551878 的 WorkOrderTracking 记录(因为这是它已经开始但尚未完成的步骤)。
因此,根据上面的数据,我想将 WorkOrders 中的新 CurrentTrackingStepId 字段设置为 WorkOrderTrackings 中具有 StartDate 值但没有 CompleteDate 值的订单的最新跟踪记录的 ID,或者,如果返回 null,我想要在具有 StartDate 值和 CompleteDate 值的最后一个 StageNum 之后的下一个 StageNum 的记录的 Id。那有意义吗?
我真的没有任何我已经尝试过的脚本,因为我无法理解如何编写它。我意识到我需要更新 dbo.WorkOrders 来设置 CurrentTrackingStepId=...something。这将需要一个 select 语句来从正确记录的 WorkOrderTrackings 表中获取 Id。它可能需要一个 EXIST 语句来检查是否存在具有开始日期但没有完整日期的记录。
解决方案
假设您的StageNum
值是连续的,您可以执行以下操作:
/* Table and data mock-up */
DECLARE @WorkOrders table ( Id int, OrderNumber varchar(10), CurrentStage int );
INSERT INTO @WorkOrders ( Id, OrderNumber ) VALUES
( 53803, '139107-00' ), ( 53781, '139094-00' );
DECLARE @WorkOrderTrackings table (
ID int, [WO-id] int, StageNum int, StartDate datetime, CompleteDate datetime
);
INSERT INTO @WorkOrderTrackings VALUES
( 551891, 53803, 1, '2020-08-28 6:33:18 PM', '2020-08-28 6:33:18 PM' ),
( 551892, 53803, 2, '2020-08-28 6:33:18 PM', '2020-08-31 8:07:09 AM' ),
( 551893, 53803, 3, '2020-08-31 8:07:17 AM', '2020-08-31 8:07:24 AM' ),
( 551894, 53803, 4, NULL, NULL ),
( 551895, 53803, 5, NULL, NULL ),
( 551896, 53803, 6, NULL, NULL ),
( 551897, 53803, 7, NULL, NULL );
/* Update the current stage of the WorkOrder based on the last completed StageNum + 1 */
UPDATE @WorkOrders
SET
CurrentStage = wt.CurrentStageID
FROM @WorkOrders wo
OUTER APPLY (
SELECT ID AS CurrentStageID FROM @WorkOrderTrackings wt
WHERE
wt.[WO-id] = wo.Id AND wt.StageNum = (
SELECT ( MAX( StageNum ) + 1 ) FROM @WorkOrderTrackings x WHERE x.[WO-id] = wt.[WO-id]
AND x.CompleteDate IS NOT NULL
)
) AS wt
WHERE
wo.[OrderNumber] = '139107-00';
/* Show the updated WorkOrders resultset */
SELECT * FROM @WorkOrders ORDER BY Id;
退货
+-------+-------------+--------------+
| Id | OrderNumber | CurrentStage |
+-------+-------------+--------------+
| 53781 | 139094-00 | NULL |
| 53803 | 139107-00 | 551894 |
+-------+-------------+--------------+
更新:
我喜欢这种方法,但请注意这
StageNum
不是连续的(第一个系列中的 1、2、5、6、7)。
此示例使用 LEAD,就像 Gert 的方法一样。
/* Update the current stage of the WorkOrder based on the last stage completed */
UPDATE @WorkOrders
SET
CurrentStage = wt.CurrentStageID
FROM @WorkOrders wo
OUTER APPLY (
SELECT TOP 1
LEAD( ID, 1, NULL ) OVER ( PARTITION BY [WO-id] ORDER BY ID, StageNum ) AS CurrentStageID
FROM @WorkOrderTrackings wt
WHERE
wt.[WO-id] = wo.Id AND wt.ID >= (
SELECT MAX( ID ) FROM @WorkOrderTrackings x WHERE x.[WO-id] = wt.[WO-id]
AND x.CompleteDate IS NOT NULL
)
ORDER BY
wt.ID, wt.StageNum
) AS wt
WHERE
wo.[OrderNumber] = '139107-00';
退货
+-------+-------------+--------------+
| Id | OrderNumber | CurrentStage |
+-------+-------------+--------------+
| 53781 | 139094-00 | NULL |
| 53803 | 139107-00 | 551894 |
+-------+-------------+--------------+
推荐阅读
- kubernetes - 如何从值中获取 2 个结构并在 Helm 中创建 JSON?
- c# - 为什么 try/finalize 块不会抛出 CS0165: Use of unassigned local variable 编译错误?
- c# - 有没有办法在非 Web C# 项目中使用 Session 变量?
- mysql - MySQL在最大化时重用错过的主键int值
- arrays - array.splice 不是函数 - 角度
- java - 在全屏Android应用程序中禁用状态栏?
- c# - 返回要在 EntityFrameworkCore 中使用的类类型
- swift - 选择图像时未捕获的异常“NSInvalidArgumentException”
- r - 如何在 R 中以给定权重创建具有两个值(或名称)的列
- amazon-quicksight - Quicksight 数据透视表排序不起作用