如何通过表循环查找数据集？

小编典典

如何通过表循环查找数据集？

sql

我必须找到以分钟为单位的timediff，以了解订单的使用寿命。也就是说，对于每个订单，从订单收到的时间（活动ID
1）到键控的时间（2）到打印的时间（3）到交货的时间（4）

例如

我完全迷失了应该采取哪种方法？？用例或if则声明?? 像这样的每个循环通过每个记录？什么是最有效的方法？

我知道一旦我获得了正确变量的日期，我就可以使用DATEDIFF。

declare @received as Datetime, @keyed as DateTime, @printed as Datetime, @Delivered as Datetime, @TurnTime1 as int
Select
IF (tblOrderActivity.ActivityID = 1) SET @received = tblOrderActivity.ActivityDate
---
----
from tblOrderActivity
where OrderID = 1

它应该告诉我@ TurnTime1 = 48分钟，因为orderID 1从接收到的（活动ID 1）到键控（活动ID 2）花了48分钟@ TurnTime2
= 29分钟，因为订单1从键控（活动ID 2）到打印花费了29分钟（活动ID 3）依此类推，针对每个订单

阅读 169

2021-05-23

共1个答案

小编典典

您可以通过数据轻松地做到这一点pivoting，它可以通过两种方式完成。

1.Conditional Aggregate用于透视数据。之后pivoting您可以datediff在不同阶段之间找到。试试这个。

SELECT orderid,Received,Keyed,Printed,Delivered,
       Datediff(minute, Received, Keyed)    TurnTime1,
       Datediff(minute, Keyed, Printed)     TurnTime2,
       Datediff(minute, Printed, Delivered) TurnTime3
FROM  (SELECT OrderID,
              Max(CASE WHEN ActivityID = 1 THEN ActivityDate END) Received,
              Max(CASE WHEN ActivityID = 2 THEN ActivityDate END) Keyed,
              Max(CASE WHEN ActivityID = 3 THEN ActivityDate END) Printed,
              Max(CASE WHEN ActivityID = 4 THEN ActivityDate END) Delivered
       FROM   Yourtable
       GROUP  BY OrderID)A

2.Pivot用于转置数据

SELECT orderid,
       [1]                        AS Received,
       [2]                        AS Keyed,
       [3]                        AS Printed,
       [4]                        AS Delivered,
       Datediff(minute, [1], [2]) TurnTime1,
       Datediff(minute, [2], [3]) TurnTime2,
       Datediff(minute, [3], [4]) TurnTime3
FROM   Yourtable
       PIVOT (Max(ActivityDate)
             FOR ActivityID IN([1],[2],[3],[4]))piv

2021-05-23