如何在嵌套查询中插入左连接?

问题描述

首先感谢帮助过这个复杂难查询的朋友们.

First of all I would like to thank the friends who helped this complex and difficult query.

我有三张桌子

表一

 StaffId     FirstName       LastName   staffType
---------------------------------------
   1          Adam            Sorme      Student 
   2          Lara            Sandra     Teacher
   3          Jack            Jones      Student

表 2

 GateId   GateName  
 ---------------------------------------
   1        frontDoor
   2        superDoor

表 3

Id transitionDate     GateId  StaffId 
 ---------------------------------------
1  2018-01-1 08:00:00    1     1
2  2018-01-1 10:00:00    2     1
3  2018-01-1 20:00:00    2     1
4  2018-01-2 07:00:00    1     2
5  2018-01-2 10:00:00    1     3
6  2018-01-9 12:00:00    2     2

我想要学生每天的第一个和最后一个动作.如果在指定日期之间没有可用的移动，则必须将值设置为 null

I want the first and last movements of students for each day. Value must be set to null if no movement is available between the specified dates

transitionDate> '2018-01-1 00:00:00 000' 
 and transitionDate< '2018-01-03 00:00:00 000'

输出:

  Id     Date    MinTransitionDate    MaxTransitionDate    FirstGateName LastGateName    StaffId    StaffType
  1   2018-01-01  2018-01-1 08:00:00 2018-01-1 20:00:00    frontDoor      superDoor         1         Student
  2   2018-01-01  null                null                  null           null             3         student
  3   2018-01-02  null                null                  null           null             1         student
  4   2018-01-02  2018-01-2 10:00:00  null                 frontDoor       null             3         student

以下查询部分有效.

select s.staffId, d.dte,
       min(t.transitionDate) as first_change,
       max(t.transitionDate) as first_change,
       max(case when seqnum_asc = 1 then gateId end) as first_gateid,
       max(case when seqnum_desc = 1 then gateId end) as last_gateid
from (select s.* from Staff s where stafftype = 'Student') s cross join
     (select distinct cast(transitionDate as date) as dte from Transitions) d left join
     (select t.*,
             row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
             row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
      from Transitions t
     ) t
     on cast(t.transitiondate as date) = d.dte and
        t.staffId = s.staffId and
        1 in (t.seqnum_asc, t.seqnum_desc)
group by s.staffId, d.dte;

这里是 SQL Fiddle.

Here is a SQL Fiddle.

如何将 firstGateName 和 LastGateName 添加到此查询结果中?

推荐答案

您只需将现有查询加入Gates 表即可获取这些名称，即

You can just join your existing query to the Gates table to get those names, i.e.

<existing query>
inner join Gates g1 on g1.gateId = (required gate id)

在您的情况下，您可以使用您拥有的总价值加入

In your case you can join using the aggregate value you've

select 
    q.*, 
    g1.GateName as first_gate_name,
    g2.GateName as last_gate_name
from 
-- use existing query as a subquery, so we can easily use the first/last_gateid values
(
    select s.staffId, d.dte,
           min(t.transitionDate) as first_change,
           max(t.transitionDate) as last_change,
           max(case when seqnum_asc = 1 then gateId end) as first_gateid,
           max(case when seqnum_desc = 1 then gateId end) as last_gateid   
    from (select s.* from Staff s where stafftype = 'Student') s cross join
         (select distinct cast(transitionDate as date) as dte from Transitions) d left join
         (select t.*,
                 row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate) as seqnum_asc,
                 row_number() over (partition by StaffId, cast(transitionDate as date) order by transitionDate desc) as seqnum_desc
          from Transitions t
         ) t
         on cast(t.transitiondate as date) = d.dte and
            t.staffId = s.staffId and
            1 in (t.seqnum_asc, t.seqnum_desc)
    group by s.staffId, d.dte
) q
-- join on the appropriate gate ids      
inner join Gates g1 on g1.gateId = q.first_gateid
inner join Gates g2 on g2.gateId = q.last_gateid