sql查询两个时间段是否有交集的情况

方法一:

select * 
from orders 
where roomid = 21 and (
(startTime >= to_Date('2018-04-17 12:00:00','yyyy-MM-dd hh24:mi:ss') AND startTime <= to_Date('2018-04-18 12:00:00','yyyy-MM-dd hh24:mi:ss')) OR 
(startTime <= to_Date('2018-04-17 12:00:00','yyyy-MM-dd hh24:mi:ss') AND endTime >= to_Date('2018-04-18 12:00:00','yyyy-MM-dd hh24:mi:ss')) OR 
(endTime >= to_Date('2018-04-17 12:00:00','yyyy-MM-dd hh24:mi:ss') AND endTime <= to_Date('2018-04-18 12:00:00','yyyy-MM-dd hh24:mi:ss'))
)

方法二:

select * 
from orders 
where roomid = 21 and NOT ((endTime < to_Date('2018-04-17 12:00:00','yyyy-MM-dd hh24:mi:ss')) OR (startTime > to_Date('2018-04-18 12:00:00','yyyy-MM-dd hh24:mi:ss')))

按日期查询:

select d.dt_,count(typeid)
from orders r,(SELECT TO_DATE('2018-04-29 12:00:00', 'yyyy-MM-dd hh24:mi:ss') + (LEVEL-1) dt_ FROM dual CONNECT BY LEVEL <=31 ) d
where r.state = 0 and NOT ((r.checkouttime < d.dt_) OR (r.resvertime > d.dt_+1-1/(24*60*60))) 
group by d.dt_ order by d.dt_

阅读更多
版权声明: https://blog.csdn.net/ozigfvwbvn/article/details/79974551
文章标签: sql oracle mysql w
个人分类: sql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭