mysql - “NOT BETWEEN”在 codeigniter 中不起作用

Question

这是我的查询

SELECT 
tbl_rooms . *, tbl_property . *, tbl_bookings.booking_Id
FROM tbl_rooms
JOIN tbl_bookings ON tbl_rooms.room_id = tbl_bookings.room_id
JOIN tbl_property ON tbl_rooms.property_id = tbl_property.property_id
where
(tbl_bookings.From_date NOT BETWEEN CAST('2016-03-02' AS DATE) AND CAST('2016-03-06' AS DATE)
 AND tbl_bookings.To_date NOT BETWEEN CAST('2016-03-02' AS DATE) AND CAST('2016-03-06' AS DATE) 
 AND tbl_bookings.From_date > CAST('2016-03-06' AS DATE) 
 AND tbl_bookings.To_date > CAST('2016-03-06' AS DATE) AND tbl_property.Ref_Property_Type_Property_Type_Id = 1)

当我尝试在codeigniter内实现此查询时我发现 BETWEEN 给出结果，但 NOT BETWEEN 没有给出任何结果，需要帮助...

编辑-codeigniter实现

    $where="tbl_bookings.From_date NOT BETWEEN CAST('2016-03-02' AS DATE) AND CAST('2016-03-06' AS DATE)
    AND tbl_bookings.To_date NOT BETWEEN CAST('2016-03-02' AS DATE) AND CAST('2016-03-06' AS DATE) 
    AND tbl_bookings.From_date > CAST('2016-03-06' AS DATE)
    AND tbl_bookings.To_date > CAST('2016-03-06' AS DATE)";     
    $this->db->select('tbl_rooms.*,tbl_property.*,tbl_bookings.booking_Id');
    $this->db->from('tbl_rooms');
    $this->db->join('tbl_bookings','tbl_rooms.room_id = tbl_bookings.room_id' );
    $this->db->join('tbl_property','tbl_rooms.property_id = tbl_property.property_id' );
    $this->db->where($where);
    $this->db->where('tbl_property.Ref_Property_Type_Property_Type_Id',1);
    $query=$this->db->get();

Answer 1

其实这两个NOT BETWEEN AND是没有用的，和下面两个条件重叠了。

该查询与以下查询等效:

SELECT 
tbl_rooms . *, tbl_property . *, tbl_bookings.booking_Id
FROM tbl_rooms
JOIN tbl_bookings ON tbl_rooms.room_id = tbl_bookings.room_id
JOIN tbl_property ON tbl_rooms.property_id = tbl_property.property_id
where
    tbl_bookings.From_date > CAST('2016-03-06' AS DATE) AND 
    tbl_bookings.To_date > CAST('2016-03-06' AS DATE) AND 
    tbl_property.Ref_Property_Type_Property_Type_Id = 1;

对于得到的空集，可能会被连接条件过滤掉。

您可以通过删除整个 where 子句来验证这一点。如果是这种情况，您需要进行相应的调整。