sql - 关于 SQL 查询的建议

Question

我目前正在做一个项目，该项目需要我使用 sql 比较两个表中的数据。基本上，它的作用是检查任何未预订的未经授权的房间使用情况。一个例子是，如果一个房间在上午 9 点到 11 点被预订，那么之后的任何事情都将被视为未经授权。传感器数据将显示房间内是否有人的数据。0 代表空，1 代表 1。

这是我的 SQL 查询：

select 
    cd.ROOMID,
    cd.DAY,
    cd.CLASSSTARTDATE1,
    cd.CLASSENDDATE1,
    count(sensorD.Timestamp) as Unauthorised_Visit
from 
    classroomData cd
inner join 
    sensorData sensorD on cast(sensorD.Timestamp as date) = cast(cd.CLASSENDDATE1 as date)
where 
    sensorD.[Occupied Status ] = '1' 
    and cast(sensorD.Timestamp as time) not between cast(cd.CLASSSTARTDATE1 as time) and cast(cd.CLASSENDDATE1 as time)
group by 
    cd.ROOMID, cd.DAY, cd.CLASSENDDATE1, cd.CLASSSTARTDATE1

我现在面临的问题是一天中的任何时间都可以预订房间。例如，房间 A 的预订时间为上午 9 点至晚上 11 点和上午 11 点至下午 1 点。不在 9-11 和 11-1 之间的任何时间都被视为未经授权。

结果数据

样本数据

上述代码的问题在于，当我指定不计算任何已预订的数据时，它将计算不在上午 9 点至上午 11 点之间的任何数据，并将与上午 11 点至下午 1 点一起计算。关于如何解决这个问题的任何建议？

这是 sql fiddle 中示例 sql 数据库的链接：http ://sqlfiddle.com/#!9/e3ea65/2

标签： sqlsql-server

Answer 1

您是否只是想在房间授权期间加入传感器数据并希望看到差异？简单的左连接就可以了。我制作公用表表达式 cte1 来表示预订的时间段并用一个条目填充它。第二个 CTE 是具有两个条目的传感器数据。

实际查询是通过左连接将传感器数据与预订数据连接起来，空值表示传感器数据在没有预订时发生。

;with cte1 as (
    select '2018-01-01 10:00' as s, '2018-01-01 12:00' as e
),
cte2 as (
    select '2018-01-01 11:00' as occup
    union all
    select '2018-01-01 13:00' as occup
)
select 
    case when cte1.s is null then 'Unauthorized' else 'Authorized' end as authorized,
    * 
from cte2
left outer join cte1 on cte2.occup between cte1.s and cte1.e

结果：

用你的小提琴安装相同的查询：

select 
    case when cd.ROOMID is null then 'Unauthorized' else 'Authorized'end as Authorized,
    cd.ROOMID,
    cd.[DAY],
    cd.CLASSSTARTDATE1,
    cd.CLASSENDDATE1,
    COUNT(sd.Timestamp) as Unauthorised_Visit,
    cast(sd.[Timestamp] as date) as dt
from 
    sensorData sd
    left join classroomData cd on sd.[Timestamp] between cd.CLASSSTARTDATE1 and cd.CLASSENDDATE1
where 
    sd.[Occupied Status] = 1
group by 
    cd.ROOMID, cd.[DAY], cd.CLASSENDDATE1, cd.CLASSSTARTDATE1, cast(sd.Timestamp as date) 

结果如下：