T-SQL中的条件where语句 [英] Conditional where statement in T-SQL

问题描述

我有一张表，该表返回一个值的历史以及当前的值，每个表都有一个日期.

I've got a table that returns the history of a value, as well as the current one, each with a date.

最早的日期是主要记录.如果更改值，则使用旧值创建新记录，并使用新值更新主记录.如果再次发生这种情况，则会创建第三个记录，其中包含现在的旧值.

The oldest date is the main record. If the value is changed, a new record is created, with the old value, and the main record is updated with the new value. If this happens again, a third record is created, which contains the now old value.

因此，如果值从4开始，更改为2，然后再次更改为1.记录将进入

So if the value starts at 4, changes to 2, then again changes to 1. The records will go

1
4
2

我当前正在表上按如下方式对其自身创建内部联接，该联接获得上面3条记录的最大日期，即2.我需要的实际值是4.最简单的分辨方法如果记录是历史记录，则TriageEndDateTime为NULL.

I'm currently creating an inner join on the table to itself as follows, which gets the max date of the 3 above records, which would be 2.. The actual value I need is the 4. The easiest way to tell if a record is a historical one is that the TriageEndDateTime is NULL.

INNER JOIN (SELECT EmergencyAttendanceId,MIN(SourceCreateDateTime) as Max_Date
                FROM FactEmergencyAttendanceTriageDetail 
                GROUP BY EmergencyAttendanceId) AS EAiD 
                ON EAiD.EmergencyAttendanceId = FactEmergencyAttendanceTriageDetail.EmergencyAttendanceId
                AND EAiD.Max_Date = FactEmergencyAttendanceTriageDetail.SourceCreateDateTime

我需要做的是选择第二条记录，但前提是第二条记录存在.所以与此类似.

What I need to do is select the second record, but only if it exists. So something along the lines of this.

 SELECT EmergencyAttendanceId,MIN(SourceCreateDateTime) as Max_Date
    FROM FactEmergencyAttendanceTriageDetail 
    WHERE IF COUNT(EmergencyAttendanceId) > 1 THEN TriageEndDateTime Is NULL ELSE NOT NULL
    GROUP BY EmergencyAttendanceId 
    inside the INNER JOIN.

有人可以帮我吗?

在上述情况下，记录2是我要跟踪的记录.

In the above case, record 2 is the one I'm after.

推荐答案

尝试一下:

    SELECT EmergencyAttendanceId


    case when count(EmergencyAttendanceId) > 1 then
          MIN
          (
          case when TriageDateTime is null then SourceCreateDateTime end 
          ) 
    else
          min(SourceCreateDateTime)
    end as max_date


    FROM FactEmergencyAttendanceTriageDetail 
    GROUP BY EmergencyAttendanceId 

这篇关于T-SQL中的条件where语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！