SQL查询透视数据%2行为%1

Question

我正在尝试编写一个将活动数据转换为汇总行的查询。例如，输入数据为activity date、description和status；如果是信息性行，则为Start、Stop或null。

以下是数据格式的示例：

ID   | ActivityID | ActivityType | ActivityDate   | Status | Activity
----------------------------------------------------------------------
701  | 26         | Start        | 02/07/13 15:16 | 10     | Run Job
728  | 26         | No Change    | 05/07/13 09:30 | 20     | Running
859  | 26         | Stop         | 22/07/13 12:45 | 30     | Error
1064 | 26         | Start        | 10/08/13 13:26 | 11     | Restarted
1524 | 26         | Stop         | 28/08/13 10:19 | 31     | Error
1785 | 26         | Stop         | 07/09/13 11:48 | 31     | Error
2205 | 26         | Start        | 17/09/13 09:05 | 10     | Restarted
2528 | 26         | Start        | 14/10/13 17:56 | 11     | Restarted
2528 | 26         | Stop         | 25/10/13 20:47 | 32     | Completed

这是预期的结果：

ActivityID | Start_Type | Start_Date | Start_Status | Start_Activity | Stop_Type | Stop_Date | Stop_Status | Stop_Activity
---------------------------------------------------------------------------------
26 | Start | 02/07/13 15:16 | 10 | Run Job   | Stop | 22/07/13 12:45 | 30 | Error
26 | Start | 10/08/13 13:26 | 11 | Restarted | Stop | 28/08/13 10:19 | 31 | Error
26 | Start | 17/09/13 09:05 | 10 | Restarted | Stop | 25/10/13 20:47 | 32 | Done

我想要得到所有的启动，并将腐蚀停止与activity_start_date等和activity_stop_date放在同一行。

我的问题是，我想要在每次开始后的第一个停止，但我也想忽略在它之前有一个开始的开始。对于瞬间，Start，Stop，Stop，Start，Start，Stop，Start将返回；Start Stop，Start Stop，Start null。

我尝试了一个左连接来加入一个开始后的第一个停止，但这也包括第二个开始匹配相同的停止两次。

我想我需要一个临时表，但我认为这是不必要的。当第一个开始设置变量，然后停止是设置变量的变量之后的第一个停止，而下一个开始是新的设置变量之后的第一个开始时，日期变量是否有效？

感谢您的帮助！

这就是我到目前为止所知道的：

SELECT
activity.ID,
activity.ActivityType                               AS StartActivityType,
activity.ActivityDate                               AS StartActivityDate,
activity.Status                                     AS StartStatus,
activity.Activity                                   AS StartActivity,
activity2.ActivityType                              AS StopActivityType,
activity2.ActivityDate                              AS StopActivityDate,
activity2.Status                                    AS StopStatus,
activity2.Activity                                  AS StopActivity

FROM tempdb..#TempTable activity

FULL OUTER JOIN #TempTable activity2
ON activity.ID = activity2.ID
AND activity2.ActivityType = 'Stop'
AND (activity2.ActivityDate > activity.ActivityDate)
AND (activity2.ActivityDate = ( SELECT MIN(activity3.ActivityDate)
FROM         tempdb..#TempTable activity3
WHERE activity.ID = activity2.ID
AND activity3.ActivityType = 'Stop'
AND activity3.ActivityDate > activity.ActivityDate))
WHERE activity.ActivityType = 'Start'

--does not have a start before
AND activity.ActivityDate > (   SELECT MAX(activity3.ActivityDate)
FROM tempdb..#TempTable activity2
                        WHERE activity2.PathwayID = activity.ID
                        AND activity2.ActivityType IN ('Stop','Start')
                        AND activity2.ActivityDate > activity.ActivityDate))

--AND activity2.ActivityDate != LAG(activity2.ActivityDate) OVER (ORDER BY activity.ActivityDate),
--AND activity.ActivityDate IS NULL

ORDER BY activity.ID ASC

Answer 1

select * from #TempTable a1
cross apply
(
select top 1 *
from
#TempTable a2
where
a2.ActivityType = 'Stop'
and a2.ActivityID = a1.ActivityID
and a2.ActivityDate > a1.ActivityDate
order by
a2.ActivityDate
) a2
where
a1.ActivityType = 'Start'
order by
a1.ActivityDate;

你的数据中有一些我不理解的奇怪之处，但这应该会让你达到90%的目标，而且比试图重新加入ActivityDate要干净得多。

Answer 2

shriop的查询几乎是正确的，如果有像2205和2528这样的ActivityType = 'Start'行，它就会失败。对于那些，需要删除重复的行(具有相同ActivityType的先例的行)。

如果操作人员使用SQLServer 2012或更高版本，则可以使用LAG完成此操作

WITH DATA AS (
  SELECT ActivityID
       , ActivityType
       , ActivityDate
       , Status
       , Activity
       , LastActivity = LAG(ActivityType, 1, 'Stop') OVER (ORDER BY ActivityDate)
  FROM   Table1
  WHERE  ActivityType IN ('Start', 'Stop')
)
SELECT a1.ActivityID
     , Start_Type = a1.ActivityType
     , Start_Date = a1.ActivityDate
     , Start_Status = a1.Status
     , Start_Activity = a1.Activity
     , Stop_Type = a2.ActivityType
     , Stop_Date = a2.ActivityDate
     , Stop_Status = a2.Status
     , Stop_Activity = a2.Activity
FROM  DATA a1
CROSS apply (SELECT top 1 *
             FROM   Table1 a2
             WHERE  a2.ActivityType = 'Stop'
               AND a2.ActivityID = a1.ActivityID
               AND a2.ActivityDate > a1.ActivityDate
             ORDER BY a2.ActivityDate) a2
WHERE a1.ActivityType = 'Start'
  AND a1.LastActivity <> a1.ActivityType
ORDER BY a1.ActivityDate;

否则，可以通过行编号和自连接来模拟LAG