作为表达式的子查询返回多行报错，如何获取所需查询结果？

bedroom_count 子查询返回多行问题 #

问题原SQL #

SELECT CASE
     WHEN p.property_type = 'APARTMENT_COMMUNITY' THEN
       (SELECT fp.bedroom_count
        FROM floor_plans fp
        WHERE fp.removed = FALSE
          AND fp.property_id = p.id)
     ELSE
       (SELECT pu.bedroom_count
        FROM property_units pu
        WHERE pu.removed = FALSE
          AND pu.property_id = p.id)
   END
 FROM properties p
 WHERE p.id = 550;

我编写了上述SQL，执行时触发报错，错误信息为：ERROR: more than one row returned by a subquery used as an expression，原因是子查询返回的bedroom_count不只有一行。
我需要拿到对应的查询结果，请问这种场景下有什么其他解决方案？

解决方案 #

报错核心原因是：放在SELECT字段位置的标量子查询要求必须返回单行单列结果，但实际业务中一个公寓社区可能对应多套户型，普通房产也可能对应多套单元，子查询自然会返回多行结果。
根据实际业务需求，可以选择以下对应方案：

• 方案1：返回所有符合条件的卧室数量，用关联查询替换标量子查询
  适用场景：需要拿到该房产下所有合法的户型/单元的卧室数量，每条结果对应一条户型/单元记录

  SELECT 
    CASE 
      WHEN p.property_type = 'APARTMENT_COMMUNITY' THEN fp.bedroom_count
      ELSE pu.bedroom_count
    END AS bedroom_count
  FROM properties p
  LEFT JOIN floor_plans fp 
    ON p.property_type = 'APARTMENT_COMMUNITY' 
    AND fp.removed = FALSE 
    AND fp.property_id = p.id
  LEFT JOIN property_units pu 
    ON p.property_type != 'APARTMENT_COMMUNITY' 
    AND pu.removed = FALSE 
    AND pu.property_id = p.id
  WHERE p.id = 550;
  

• 方案2：返回聚合统计结果，用聚合函数包裹子查询
  适用场景：只需要统计值，比如所有卧室数量的去重列表、最大值、最小值、总数等
  示例1：拿到所有卧室数量的数组（PostgreSQL语法，其他数据库可替换为group_concat等对应函数）

  SELECT CASE
       WHEN p.property_type = 'APARTMENT_COMMUNITY' THEN
         ARRAY_AGG(DISTINCT fp.bedroom_count)
       ELSE
         ARRAY_AGG(DISTINCT pu.bedroom_count)
     END AS bedroom_count_list
   FROM properties p
   LEFT JOIN floor_plans fp ON fp.removed = FALSE AND fp.property_id = p.id
   LEFT JOIN property_units pu ON pu.removed = FALSE AND pu.property_id = p.id
   WHERE p.id = 550
   GROUP BY p.id, p.property_type;
  

  示例2：拿到最大的卧室数量

  SELECT CASE
       WHEN p.property_type = 'APARTMENT_COMMUNITY' THEN
         (SELECT MAX(fp.bedroom_count) FROM floor_plans fp WHERE fp.removed = FALSE AND fp.property_id = p.id)
       ELSE
         (SELECT MAX(pu.bedroom_count) FROM property_units pu WHERE pu.removed = FALSE AND pu.property_id = p.id)
     END AS max_bedroom_count
   FROM properties p
   WHERE p.id = 550;
  

• 方案3：仅取任意一条符合条件的结果，子查询加LIMIT 1
  适用场景：只要有一个卧室数量即可，不关心具体返回哪一条

  SELECT CASE
       WHEN p.property_type = 'APARTMENT_COMMUNITY' THEN
         (SELECT fp.bedroom_count FROM floor_plans fp WHERE fp.removed = FALSE AND fp.property_id = p.id LIMIT 1)
       ELSE
         (SELECT pu.bedroom_count FROM property_units pu WHERE pu.removed = FALSE AND pu.property_id = p.id LIMIT 1)
     END AS bedroom_count
   FROM properties p
   WHERE p.id = 550;
  

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