我计划使用内部连接和case将3个表连接在一起。
背景:我正在尝试首先通过deviceid进行联接device
和devicemessage
表链接。
接下来是timetable
使用roomid
和classstartdate
以及和classenddate
列将前2个表链接到该表。
我希望它显示既匹配roomid的行,又匹配表中的localtime
列,该行devicemessage
介于ClassStartDate
和ClassEndDate
来自时间表表之间。
我知道,因为内部联接仅匹配的行显示,我也希望我的视图来显示这些合并行device
和devicemessage
表其localtime
列的值不介于classstartdate
并classenddate
从timetable
表。Timetable
表中的列在视图中为null或为空。
设备表具有以下列:RoomID,RoomName,DeviceID
DeviceMessageTable具有以下列:DeviceID,Occupancy,LocalTime
时间表包含以下列:SubjectCode,Day,ClassStartDate,ClassEndDate,RoomID
我目前拥有的代码:
Create View v_ClassroomOccupancy
AS
Select A.[RoomCode],A.[RoomName],A.[DeviceID],
B.[Occupancy],B.[LocalTime],
C.[CLASSGROUP],C.[DAY], C.[CLASSSTARTDATE], C.[CLASSENDDATE], C.[DURATION],
CASE
WHEN (B.LocalTime BETWEEN C.CLASSSTARTDATE AND C.CLASSENDDATE)
AND B.Occupancy = 1 THEN 'Booked And Occupied'
WHEN (B.LocalTime NOT BETWEEN C.CLASSSTARTDATE AND C.CLASSENDDATE)
AND B.Occupancy = 1 THEN 'Not Booked but Occupied'
WHEN (B.LocalTime BETWEEN C.CLASSSTARTDATE AND C.CLASSENDDATE)
AND B.Occupancy = 0 THEN 'Booked but Not Occupied'
ELSE 'Not booked and Not Occupied'
END AS ClassroomStatus
From Device A
INNER JOIN DeviceMessageHistory B ON B.DeviceID=A.DeviceID
INNER JOIN ClassTimeTable C ON (B.LocalTime between C.CLASSSTARTDATE and C.CLASSENDDATE) WHERE C.ROOMID = A.RoomCode;
我试过了,但结果是一个空表。
您需要一个left join
,但其措辞应为:
SELECT . . .
FROM Device d JOIN
DeviceMessageHistory dmh
ON dmh.DeviceID = d.DeviceID JOIN
ClassTimeTable ctt
ON dmh.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE AND
ctt.ROOMID = d.RoomCode
注意:WHERE
子句中的逻辑需要移到该ON
子句。
我还更改了表别名。学习使用有意义的表别名(表缩写)而不是任意字母。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句