MySQL CASE关联查询求助：构建含可用性字段与一对多关系的查询

看起来你遇到的核心问题是子查询返回多行导致主查询无法正常执行——因为一个储物间可能对应多个订单记录，直接用LEFT JOIN的子查询会返回多条结果，没法和主查询的单条储物间记录匹配。下面给你两种简洁的解决思路，先理清楚逻辑再写代码：

先明确核心逻辑 #

首先得确认你的可用性规则：从你尝试的代码看，你似乎想把「已到释放日期（release_date<=今天）」或者「有状态为3/4的预订订单」都标记为1？还是反过来——有有效预订的储物间应该标记为不可用？我先假设后者（更符合常规业务逻辑：已预订的储物间不能被再占用），如果你的逻辑不同，只需要调整CASE分支即可。

方法1：用EXISTS子查询（推荐，高效简洁） #

EXISTS可以快速判断当前储物间是否存在符合条件的订单，不会返回多行，完美解决你的问题：

SELECT 
    sr.*,
    CASE
        -- 优先判断：如果有状态为3/4的已预订订单，标记为不可用（这里返回0，你可以改成自己需要的值）
        WHEN EXISTS (
            SELECT 1 
            FROM orders o 
            WHERE o.storage_room_id = sr.id 
              AND o.status_id IN (3, 4)
        ) THEN 0
        -- 保留你原来的release_date判断逻辑
        WHEN sr.release_date <= CURDATE() THEN 1
        WHEN sr.release_date <= ADDDATE(CURDATE(), INTERVAL 30 DAY) THEN sr.release_date
        ELSE 0
    END AS available
FROM storage_rooms sr;

如果你的逻辑是「已到释放日期或有有效预订则标记为1」，只需要把CASE的第一个分支改成：

WHEN sr.release_date <= CURDATE() 
     OR EXISTS (
         SELECT 1 
         FROM orders o 
         WHERE o.storage_room_id = sr.id 
           AND o.status_id IN (3, 4)
     ) THEN 1

方法2：用LEFT JOIN + GROUP BY（适合需要聚合订单其他数据的场景） #

如果之后你还需要统计订单的其他信息，可以用这种方式，但需要注意GROUP BY要包含storage_rooms的所有字段（不同数据库规则略有差异，比如MySQL关闭ONLY_FULL_GROUP_BY时可以只按主键分组，但推荐显式写出所有字段）：

SELECT 
    sr.*,
    CASE
        -- 用MAX判断是否存在状态3/4的订单：只要有一个符合，MAX返回1
        WHEN MAX(CASE WHEN o.status_id IN (3,4) THEN 1 ELSE 0 END) = 1 THEN 0
        WHEN sr.release_date <= CURDATE() THEN 1
        WHEN sr.release_date <= ADDDATE(CURDATE(), INTERVAL 30 DAY) THEN sr.release_date
        ELSE 0
    END AS available
FROM storage_rooms sr
LEFT JOIN orders o ON sr.id = o.storage_room_id
-- 这里要列出storage_rooms的所有实际字段，比如sr.id, sr.release_date, sr.name, sr.capacity...
GROUP BY sr.id, sr.release_date, sr.name, sr.capacity
ORDER BY sr.id;

为什么你的原查询失败？ #

你写的子查询(SELECT ... FROM storage_rooms LEFT OUTER JOIN orders ...)会返回所有储物间关联的订单记录，当一个储物间有多个订单时，子查询就会返回多行，而主查询的每一行只能对应一个值，所以数据库会报错「Subquery returns more than 1 row」。用EXISTS或者GROUP BY聚合的方式，就能把多行订单记录转换成单个判断结果，匹配主查询的单条储物间记录。

内容的提问来源于stack exchange，提问作者DerpyMongoose