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

版权声明: https://blog.csdn.net/ozigfvwbvn/article/details/79974551

方法一:

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_

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

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试

关闭