SQL JOIN-从第二个表中检索MAX DateTime，并在前一个MAX之后检索第一个DateTime以获取其他值

问题描述

我在创建正确的SQL表达式时遇到问题.

I have issue with creating a proper SQL expression.

我的表TICKET的列为TICKETID

TICKETID    
1000
1001

然后我有了STATUSHISTORY表，从那里我可以检索到该票证进入VENDOR状态(上一个VENDOR状态)和退出VENDOR状态(通过退出VENDOR状态时的最后一次)(最大时间)的意思是上一个下一个INPROG状态，但仅在VENDOR状态之后是第一个INPROG，它始终是INPROG在VENDOR状态之后的下一个状态).此外，STATUSHISOTRY中可能根本不存在ID的VENDOR状态(然后应返回null)，但是INPROG始终存在-如果ID不再处于VENDOR状态，则它可以在VENDOR状态之前和之后.这是STATUSHISTORY的示例.

I then have table STATUSHISTORY from where I need to retrieve what was the last time (maximum time) when that ticket entered VENDOR status (last VENDOR status) and when it exited VENDOR status (by exiting VENDOR status I mean the first next INPROG status, but only first INPROG after the VENDOR status, it's always INPROG the next status after VENDOR status). Also it is also possible that VENDOR status for ID does not exist at all in STATUSHISOTRY (then nulls should be returned), but INPROG exists always - it can be before but also and after VENDOR status, if ID is not anymore in VENDOR status.Here is the example of STATUSHISTORY.

ID  TICKETID    STATUS    DATETIME
1   1000        INPROG    01.01.2017 10:00
2   1000        VENDOR    02.01.2017 10:00
3   1000        INPROG    03.01.2017 10:00
4   1000        VENDOR    04.01.2017 10:00
5   1000        INPROG    05.01.2017 10:00
6   1000        HOLD      06.01.2017 10:00
7   1000        INPROG    07.01.2017 10:00
8   1001        INPROG    02.02.2017 10:00
9   1001        VENDOR    03.02.2017 10:00
10  1001        INPROG    04.02.2017 10:00
11  1001        VENDOR    05.02.2017 10:00

因此，从TICKET表进行查询并对STATUSHISTORY表进行JOIN时的结果应为:

So the result when doing the query from TICKET table and doing the JOIN with table STATUSHISTORY should be:

ID     VENDOR_ENTERED      VENDOR_EXITED
1000   04.01.2017 10:00    05.01.2017 10:00
1001   05.02.2017 10:00    null

因为ID为1000，最后一个VENDOR状态为04.01.2017，并且 first INPROG状态是该ID的VENDOR状态之后为05.01.2017，而ID 1001的最后一个VENDOR状态为在05.02.2017处，此后仍未发生INPROG状态.如果VENDOR不存在，则两个列的结果都应该为null.我真的很坚持这一点，尝试使用不同的JOIN，但是没有任何进展.如果您能帮助我，请先谢谢您.

Because for ID 1000 last VENDOR status was at 04.01.2017 and the first INPROG status after the VENDOR status for that ID was at 05.01.2017 while for ID 1001 the last VENDOR status was at 05.02.2017 and after that INPROG status did not happen yet.If VENDOR did not exist then both columns should be null in result.I am really stuck with this, trying different JOINs but without any progress.Thank you in advance if you can help me.

推荐答案

您可以使用窗口函数执行此操作.首先，为票证分配一个供应商"组.您可以使用累加的总和来计算每条记录上或每条记录上的供应商"记录的数量.

You can do this with window functions. First, assign a "vendor" group to the tickets. You can do this using a cumulative sum counting the number of "vendor" records on or before each record.

然后，汇总记录以使每个供应商"组获得一个记录.并使用行号获取最新记录.所以:

Then, aggregate the records to get one record per "vendor" group. And use row numbers to get the most recent records. So:

with vg as (
      select ticket,
             min(datetime) as vendor_entered,
             min(case when status = 'INPROG' then datetime end) as vendor_exitied
      from (select sh.*,
                   sum(case when status = 'VENDOR' then 1 else 0 end) over (partition by ticketid order by datetime) as grp
            from statushistory sh
           ) sh
      group by ticket, grp
     )
select vg.tiketid, vg.vendor_entered, vg.vendor_exited
from (select vg.*,
             row_number() over (partition by ticket order by vendor_entered desc) as seqnum
      from vg
     ) vg
where seqnum = 1;

这篇关于SQL JOIN-从第二个表中检索MAX DateTime，并在前一个MAX之后检索第一个DateTime以获取其他值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！