这是我的数据库结构
Table name :set_inventory, and columns are below
inventory_id | room_id | quantity_start_date | quantity_end_date | total_rooms
1 | 2 | 2015-10-10 | 2015-10-12 | 5
2 | 2 | 2015-10-13 | 2015-10-14 | 10
3 | 2 | 2015-10-15 | 2015-10-17 | 0
另一张表
Table name : rooms, amd columns are
room_id | room_type | room_picture | room_description
2 | standard | pic_link | demo description
说明:在库存表管理中,可以根据多个日期范围设置库存。
我的查询:
SELECT rooms.room_id, rooms.room_pic1, rooms.room_type, rooms.maximum_adults,
rooms.maximum_children, rooms.room_amenities,set_inventory.room_id,
set_inventory.quantity_start_date, set_inventory.quantity_start_date,
set_inventory.total_rooms
from rooms, set_inventory
WHERE rooms.room_id = set_inventory.room_id
AND quantity_start_date <= '2015-10-11'
AND quantity_end_date > '2015-10-13'
它只显示了我的库存5,基于上面的查询,
我在寻找什么:实际上,我在寻找结果,例如,我的check_in_date"2015-10-11"和check_out_date是"2015-10-17"在这种情况下,我将能够通知客户"房间只提供4天,
请帮帮我,谢谢
更新
尝试此WHERE
条件:
WHERE rooms.room_id = set_inventory.room_id AND DATE(quantity_start_date) <= '2015-10-11' AND DATE(quantity_end_date) > '2015-10-13';
所以,考虑到你想知道从这些日期到日期之间的天数,你的查询应该包含这样的内容:
SELECT ABS(DATEDIFF(a1.start_date, a2.end_date)) FROM (
SELECT quantity_start_date as start_date FROM set_inventory WHERE quantity_start_date <= '2015-10-11' ORDER BY start_date DESC LIMIT 1) a1, (
SELECT quantity_end_date as end_date FROM set_inventory WHERE quantity_end_date > '2015-10-13' ORDER BY end_date ASC LIMIT 1) a2;
若你们尝试一下,结果会得到4
。