左连接，只检索最近的记录

Question

这只是示例情况，我原来的表更复杂。

表

| SchoolId| ClubId | ChildID|TeacherId|AttendanceDate|IsPresent|
|:------- |:------:| :-----:|:-------:|:------------:|:-------:|
| A       | 1      | 1      |1        |22-MAY-2022   |1        |
| A       | 1      | 2      |1        |22-MAY-2022   |0        |
| A       | 1      | 3      |1        |22-MAY-2022   |1        |
| B       | 2      | 11     |2        |22-MAY-2022   |1        |
| B       | 2      | 22     |2        |22-MAY-2022   |0        |
| B       | 2      | 33     |2        |22-MAY-2022   |0        |

表B

|ChildID|TeacherId |CreateOn        |IsPresent|ReasonId |
|:-----:|:-------: |:--------------:|:-------:|:-------:|
|2      |1         |22-MAY-2022     |0        |1        |
|2      |1         |23-MAY-2022     |0        |2        |
|22     |2         |22-MAY-2022     |0        |2        |
|33     |2         |22-MAY-2022     |0        |3        |

表C

| ReasonId | ReasonMaster   |
| -------- | -------------- |
| 1        | Health         |
| 2        | Social         |
| 3        | Unknown        |

我想要这样的左连接结果:

| SchoolId| ClubId | ChildID|TeacherId|AttendanceDate|IsPresent|ReasonId |ReasonMaster |
|:------- |:------:| :-----:|:-------:|:------------:|:-------:|:-------:|:------------:
| A       | 1      | 1      |1        |22-MAY-2022   |1        |         |             |
| A       | 1      | 2      |1        |22-MAY-2022   |0        |2        |Social       |
| A       | 1      | 3      |1        |22-MAY-2022   |1        |         |             |
| B       | 2      | 11     |2        |22-MAY-2022   |1        |         |             |
| B       | 2      | 22     |2        |22-MAY-2022   |0        |2        |Social       |
| B       | 2      | 33     |2        |22-MAY-2022   |0        |3        |Unknown      |

以下是我的案例:

1. 我只想根据表B上的create on列从表B检索到表a的最新数据，因为有用户的重复输入。例如，在表B中，child Id= 2 &老师Id = 1。
2. 如果表A中的状态，列IsPresent=0，我只需要检索数据
从C表中有一个额外的数据导致主。

Answer 1

试试这个

假设MS SQL Server

--step 1 - Get the latest rec from table B 
;with MaxCreate as ( Select ChildID,TeacherID,Max(CreateOn) as MaxCreateOn
from TableB
group by ChildID,TeacherID)
,LatestCreate as (select TableB.ChildID, TableB.TeacherId, TableB.CreateOn, TableB.IsPresent, TableB.ReasonId
from TableB
inner join MaxCreate
on TableB.ChildID = MaxCreate.ChildID
and TableB.TeacherId = MaxCreate.TeacherId
and TableB.CreateOn = MaxCreate.MaxCreateOn)
-- Now Join the latest to the other tables

Select 
TableA.SchoolId
,TableA.ClubId
,TableA.ChildID
,TableA.TeacherId
,TableA.AttendanceDate
,TableA.IsPresent
,LatestCreate.ReasonId
,TableC.ReasonMaster
From 
TableA Left join LatestCreate
on TableA.ChildID = LatestCreate.ChildID
and TableA.TeacherID = LatestCreate.TeacherID
left join TableC
on LatestCreate.ReasonId = TableC.ReasonId

非CTE方法

Select 
TableA.SchoolId
,TableA.ClubId
,TableA.ChildID
,TableA.TeacherId
,TableA.AttendanceDate
,TableA.IsPresent
,LatestCreate.ReasonId
,TableC.ReasonMaster
From 
TableB
inner join
( Select ChildID,TeacherID,Max(CreateOn) as MaxCreateOn
from TableB
group by ChildID,TeacherID) as MaxCreate
on TableB.ChildID = MaxCreate.ChildID
and TableB.TeacherId = MaxCreate.TeacherId
and TableB.CreateOn = MaxCreate.MaxCreateOn
Left join TableA  
on TableA.ChildID = TableB.ChildID
and TableA.TeacherID = TableB.TeacherID
left join TableC
on TableB.ReasonId = TableC.ReasonId