问题描述
首先感谢帮助过这个复杂难查询的朋友们.
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