我有一个sql查询:
SELECT (CASE WHEN count(_days) > 0 THEN 'yes' ELSE 'No' END) as availability
FROM (
SELECT count(roomTypeDay2.room_type_id) as _days
FROM room_type_day as roomTypeDay2
LEFT JOIN room_type as roomType2 on roomTypeDay2.room_type_id = roomType2.id
WHERE roomType2.accommodation_id=3
AND roomTypeDay2.date IN ( '2018-06-09 00:00:00','2018-06-10 00:00:00','2018-06-11 00:00:00')
GROUP BY roomTypeDay2.room_type_id
HAVING COUNT(roomTypeDay2.room_type_id) = 3
) as disponible
这很好,但是现在,我想过滤第一个日期行(2018-06-09 00:00:00)是否
“最短住宿时间<=3天,最长释放时间<=2天”
参数,但我不知道怎么做。
我尝试将以下行添加到查询:
和(roomtypeday2.date='2018-06-09 00:00:00'和roomtypeday2.min\u night\u stay<=3和roomtypeday2.release\u days<=2)
但这不是正确的查询。
乳突
SELECT (CASE WHEN count(_days) > 0 THEN 'yes' ELSE 'No' END) as availability
FROM (
SELECT count(roomTypeDay2.room_type_id) as _days
FROM room_type_day as roomTypeDay2
LEFT JOIN room_type as roomType2 on roomTypeDay2.room_type_id = roomType2.id
AND roomType2.accommodation_id=3
WHERE roomTypeDay2.date IN ( '2018-06-09 00:00:00','2018-06-10 00:00:00','2018-06-11 00:00:00')
GROUP BY roomTypeDay2.room_type_id
HAVING COUNT(roomTypeDay2.room_type_id) = 3
) as disponible
解决方案
SELECT (CASE WHEN count(_days) > 0 THEN 'yes' ELSE 'No' END) as availability
FROM (
SELECT count(roomTypeDay2.room_type_id) as _days
FROM room_type_day as roomTypeDay2
LEFT JOIN room_type as roomType2 on roomTypeDay2.room_type_id = roomType2.id
WHERE roomType2.accommodation_id=3
AND roomTypeDay2.num_rooms_available > 0
AND (roomTypeDay2.date = '2018-06-12 00:00:00' AND roomTypeDay2.min_night_stay <= 2 AND roomTypeDay2.release_days <= 2 )
OR roomTypeDay2.date IN ( '2018-06-13 00:00:00','2018-06-14 00:00:00')
GROUP BY roomTypeDay2.room_type_id
HAVING COUNT(roomTypeDay2.room_type_id) = 3
) as disponible
2条答案
按热度按时间v9tzhpje1#
这是你的
WHERE
条款:现在,对于第一次约会,您需要附加条件。使用
AND
以及OR
带圆括号:blmhpbnm2#