postgresql 如何从用作表达式的子查询中返回多行?

9bfwbjaz  于 2023-03-01  发布在  PostgreSQL
关注(0)|答案(3)|浏览(134)

bedroom_count不是单行

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;

我有这个,bedroom_count不是一个单行,所以它给出这个错误ERROR:用作表达式的子查询返回多行

    • 我需要得到这个结果,在这种情况下,是否有其他解决方案?**
v8wbuo2f

v8wbuo2f1#

错误来自于第一个或第二个子查询返回给定property_id(550)的多行。
我想要所有的结果
我猜您需要的是两个表的左连接。

select p.property_type, coalesce(fp.bedroom_count, pu.bedroom_count) 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
qojgxg4l

qojgxg4l2#

听起来你真的很想连接这些表。但是,当你想从一个表或另一个表计算卧室数时,你必须外部连接这些表。

select p.*, coalesce(fp.bedroom_count, pu.bedroom_count) as bedroom_count
from properties p
left join floor_plans fp on p.property_type = 'APARTMENT_COMMUNITY' 
                         and fp.property_id = p.id
                         and fp.removed = false 
left join property_units pu on p.property_type <> 'APARTMENT_COMMUNITY' 
                            and pu.property_id = p.id
                            and pu.removed = false 
where p.id = 550
order by p.id;

或者使用UNION ALL

select p.*, fp.bedroom_count
from properties p
join floor_plans fp on fp.property_id = p.id and fp.removed = false 
where p.id = 550
and p.property_type = 'APARTMENT_COMMUNITY'
union all
select p.*, pu.bedroom_count
from properties p
join property_units pu on pu.property_id = p.id and pu.removed = false 
where p.id = 550
and p.property_type <> 'APARTMENT_COMMUNITY'
order by p.id;

(If property_type可以为空,因此需要对这些查询进行一些调整来处理此问题。)

yws3nbqq

yws3nbqq3#

select  case 
            when p.property_type ='APARTMENT_COMMUNITY' 
                then (  
                    select  array_agg(distinct fp.bedroom_count) 
                    from    floor_plans fp 
                    where   fp.removed = false 
                    and     fp.property_id=p.id ) 
            else (
                    select  (array_agg(distinct 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

这是我的问题的答案,以防有人需要

相关问题