如何检查记录在 MSSQL 中的一对多联接中是否具有最旧的时间戳

Question

我有两个表，分别名为事件和操作。每个事件都有许多操作。每个操作都有一个完成时间的时间戳。

我想选择所有操作，如下所示：

select action.completed, * from action
left join incident on incident.id = action.incidentid

然后我想生成一个布尔值，告诉我每个操作是否是每个事件完成的第一个操作。

示例：对于事件 2，首先完成了操作 6。对于事件 3，首先完成了操作 10。这是我想要的输出：

completed            | completed_first | actionid | incidentid | ...
---------------------+-----------------+----------+------------+-----
2017-01-01 02:23:30  | false           |     5    |    2       |
2017-01-01 01:00:00  | true            |     6    |    2       |
2017-01-01 05:23:30  | false           |     7    |    2       |
2017-01-01 00:30:00  | false           |     8    |    3       |
NULL                 | false           |     9    |    3       |
2017-01-01 00:10:00  | true            |    10    |    3       |

那么如何生成completed_first列呢？

请注意，已完成 = NULL 不应符合已完成的条件。

Answer 1

在 CTE 和案例中Row_number：

with ACTION_T as
(
select a1.*, 
       row_number() over(partition by incidentid 
                         order by coalesce(completed,
                                           getdate())) as a_ord  -- Using a getdate() to handle nulls
from action a1
)
select completed,
       case when a_ord = 1 then 'true' else 'false' end as completed_first,
       actionid,
       incidentid
from ACTION_T

Answer 2

希望，我理解正确。

请查看以下查询

  SELECT completed ,
  CASE
    WHEN rn = 1
    THEN 'true'
    ELSE 'false'
  END AS completed_first ,
  actionid ,
  incidentid
FROM
  (SELECT action.completed,
    actionid ,
    incidentid ,
    row_number() over (partition BY incidentid order by coalesce(action.completed,DATEADD(day, 1, GETDATE()))) rn
  FROM action
  LEFT JOIN incident
  ON incident.id = action.incidentid
  ) a

Answer 3

    Create table [action] (Actionid int, incidentid int,  [Completed] datetime)
    Create table Incident (Incidentid int)
    insert into [incident] values (2), (3)
    insert into [action] values (5 ,2 ,'2017-01-01 02:23:30')
                                , (6, 2,'2017-01-01 01:00:00')
                                , (7, 2,'2017-01-01 05:23:30')
                                , (8, 3,'2017-01-01 00:30:00')
                                , (9, 3, null)
                                , (10, 3, '2017-01-01 00:10:00')

如果每个事件没有多个相同的时间：

select action.completed, iif(First.incidentid is null, 'False', 'True') as [Completed_First], action.Actionid, action.incidentID, incident.*
from action
left join incident on incident.Incidentid = action.incidentid
left join (
Select IncidentID, min(completed) as F_Completed
from ACTION
Group BY IncidentID ) First on action.incidentid=first.incidentID and action.completed=first.F_Completed

适应具有多个相同时间的事件：

select action.completed, iif(First.M_Actionid is null, 'False', 'True') as [Completed_First], action.Actionid, action.incidentID, incident.*
from action
left join incident on incident.Incidentid = action.incidentid
left join (
Select min(ActionID) as M_ActionID
from action
inner join (
select  incidentid, min(completed) as M_Completed
from action
group by incidentid) ICMin on action.incidentID =  icmin.incidentID and action.completed= icmin.m_completed
group by action.incidentid ) First on action.actionid=first.M_ActionID