mysql - 添加语句 WHEN CASE SQL

Question

我有这样的表:

这是我显示房间可用性的查询

SELECT 
hotel.nama_hotel,
hotel.kota,
room.id_room,
room.position,
room.price,
room_price.date_start,
room_price.date_end,
room_price.price
FROM room
    JOIN hotel on room.id_hotel = hotel.id_hotel
    JOIN room_price on room.id_room = room_price.id_room
    WHERE 
        (
            room.id_room NOT IN 
            (
            SELECT id_room FROM booking 
                WHERE 
                (
                    booking_start BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    booking_end   BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    '2018-02-10'  BETWEEN booking_start AND booking_end OR
                    '2018-02-11'  BETWEEN booking_start AND booking_end
                ) 
            )
        )
    AND (room.status = 1) 
    AND (hotel.kota="Bandung")
    AND (room.position = "earth")

我从表 room 和表 room_price 中得到了 2 列价格

当插入日期介于 room_price.date_start 和 room_price.date_end 之间并且价格出现在 room_price 表中时，我需要 1 列价格当 room_price.date_start 和 room_price.date_end 之间不存在时，价格从表 room

中显示

可以吗？

编辑

这是查询

SELECT 
hotel.nama_hotel,
hotel.kota,
room.id_room,
room.position,
room_price.date_start,
room_price.date_end,
    (
        CASE WHEN 
        (
            '2018-02-10' BETWEEN room_price.date_start AND room_price.date_end
        )THEN  
        room.price = room_price.price
        ELSE room.price = room.price END
    ) as price from room 



   JOIN hotel on room.id_hotel = hotel.id_hotel
    JOIN room_price on room.id_room = room_price.id_room
    WHERE 
        (
            room.id_room NOT IN 
            (
            SELECT id_room FROM booking 
                WHERE 
                (
                    booking_start BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    booking_end   BETWEEN '2018-02-10' AND '2018-02-11' OR 
                    '2018-02-10'  BETWEEN booking_start AND booking_end OR
                    '2018-02-11'  BETWEEN booking_start AND booking_end
                ) 
            )
        )
    AND (room.status = 1) 
    AND (hotel.kota="Bandung")
    AND (room.position = "earth")

但我没有得到 price = room_price.price

Answer 1

你在room.price之后的第9行之后错过了一个,，当你打开一个括号(时，SQL认为你正在调用一个函数 room.price()