使用 CASE 更改 SELECT 返回值

Question

任何人都可以帮我解决这个问题吗？我试图获取具有房间容量 * 数量的对象中的珠子数量

SELECT 
object.id, object.name, 
location.address, 
location.postcode, 
location_city.`name`,

(
select
    case
            when object_room.object_room_type_id = 1 then (1 * object_room.quantity)
            when object_room.object_room_type_id = 2 then (2 * object_room.quantity)
            when object_room.object_room_type_id = 3 then (3 * object_room.quantity)
            when object_room.object_room_type_id = 4 then (5 * object_room.quantity)
            when object_room.object_room_type_id = 5 then (1 * object_room.quantity)
            when object_room.object_room_type_id = 6 then (4 * object_room.quantity)
            when object_room.object_room_type_id = 8 then (2 * object_room.quantity)
            when object_room.object_room_type_id = 9 then (3 * object_room.quantity)
    end CASE
from object_room

)



FROM object  
LEFT JOIN location ON object.location_id = location.id 
LEFT JOIN location_city ON location.location_city_id = location_city.id 
LEFT JOIN object_room ON object.id = object_room.object_id

我收到此错误：

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE
from object_room

Answer 1

请阅读 有关 case 的 MySQL 手册声明：

case object_room.object_room_type_id
        when 1 then (1 * object_room.quantity)
        when 2 then (2 * object_room.quantity)
        when 3 then (3 * object_room.quantity)
        when 4 then (5 * object_room.quantity)
        when 5 then (1 * object_room.quantity)
        when 6 then (4 * object_room.quantity)
        when 8 then (2 * object_room.quantity)
        when 9 then (3 * object_room.quantity)
end

Answer 2

您要么拥有一张object_room_type 表，要么您应该创建一个表。我假设您将房间容量列称为“容量”。然后您可以在查询中加入 object_room_type 表：

SELECT 
object.id, object.name, 
location.address, 
location.postcode, 
location_city.`name`,
object_room_type.capacity * object_room.quantity
FROM object  
LEFT JOIN location ON object.location_id = location.id 
LEFT JOIN location_city ON location.location_city_id = location_city.id 
LEFT JOIN object_room ON object.id = object_room.object_id
LEFT JOIN object_room_type ON object_room_type.object_room_type_id = object_room.object_room_type_id