时间戳

首页 > 解决方案 > 限制每列值的 sql 结果

sql-server - 限制每列值的 sql 结果

问题描述

我正在尝试为游标建立一个选择语句。

我的表有车辆事件，对于我的光标，我在确定的时间段内抓住所有点火和点火事件。

我想通过联合将每辆车的那个时间段的第一个和最后一个事件联合到我的选择中，我的想法是，该事件的 where 子句应该是该事件不能打开或关闭，因此它不会重复。但我不知道如何为每辆车获取一个事件。

我目前拥有的是以下内容。

SELECT  
    al.ActivityLogID,
    al.[VehicleID],
    al.[DriverID],
    al.[ActivityDateTime],
    al.[EventSubTypeId]
FROM    
    [activitylog] a
WHERE   
    [ActivityDateTime] BETWEEN @StartDateTime AND @EndDateTime AND
    [EventSubTypeID] = 3 -- ignitionON

UNION

SELECT  
    al.ActivityLogID,
    al.[VehicleID],
    al.[DriverID],
    al.[ActivityDateTime],
    al.[EventSubTypeId]
FROM    
    [activitylog] a
WHERE   
    [ActivityDateTime] BETWEEN @StartDateTime AND @EndDateTime AND
    [EventSubTypeID] = 4 -- ignitionOFF
ORDER BY 
    [VehicleID], [ActivityDateTime]

标签： sql-serverunion

解决方案

假设 IgnitionOFF 在 IgnitionON 日期时间之后

SELECT  al.[VehicleID]
      , al.[DriverID]
      , MIN(al.[ActivityDateTime])
      , MAX(al.[ActivityDateTime])
FROM  [activitylog] al
WHERE [ActivityDateTime] BETWEEN @StartDateTime AND @EndDateTime AND
      [EventSubTypeID] IN (3,4) -- ignitionON/OFF
GROUP BY al.[VehicleID]
       , al.[DriverID]
ORDER BY al.[VehicleID]
       , al.[DriverID]

或者你可能需要这个：

SELECT [VehicleID]
      ,[EventSubTypeID]
      ,[ActivityDateTime]
FROM
(
  SELECT  al.[VehicleID]
        , [EventSubTypeID] 
        , MIN(al.[ActivityDateTime]) AS "ActivityDateTime"
  FROM  [activitylog] al
  WHERE [ActivityDateTime] BETWEEN @StartDateTime AND @EndDateTime AND
        [EventSubTypeID] = 3 -- ignitionON
  GROUP BY al.[VehicleID], [EventSubTypeID]
  UNION
  SELECT  al.[VehicleID]
        , [EventSubTypeID]
        , MAX(al.[ActivityDateTime]) AS "ActivityDateTime" 
  FROM  [activitylog] al
  WHERE [ActivityDateTime] BETWEEN @StartDateTime AND @EndDateTime AND
        [EventSubTypeID] = 4 -- ignitionOFF
  GROUP BY al.[VehicleID], [EventSubTypeID]
)
ORDER BY [VehicleID], [EventSubTypeID]

推荐阅读