如何为添加到select的子查询返回多个列？

83qze16e 于 2021-07-29 发布在 Java

关注(0)|答案(1)|浏览(318)

我有这样一个问题：

select 
   e.field1,
   e.field2,
   (select count(field3) from tbl1 where someField = e.field1 group By someType ) as count_1,
   (select count(field4) from tbl1 where someField = e.field1 group By someType ) as count_2,
from
...

我不喜欢代码重复计数，但如果我做smth喜欢

select count(field3), count(field3) ....

在子查询中，postgres抱怨子查询必须返回单个列。
如何修复？

sql postgresql count subquery select

来源：https://stackoverflow.com/questions/62694224/how-to-return-several-columns-for-subquery-added-to-select

1条答案

按热度按时间

4uqofj5v1#

可以使用横向连接：

select e.field1,  e.field2, t1.*
from e left join lateral
     (seclect count(tbl1.field3) as count_1, count(tbl1.field4) as count_4
      from tbl1
      where tbl1.someField = e.field1
     ) t1
     on true;

您还可以通过聚合来实现这一点：

select e.field1,  e.field2, t1.count_1, t1.count_2
from e left join
     (select tbl1.someField, count(tbl1.field3) as count_1, count(tbl1.field4) as count_2
      from tbl1
      group by tbl1.someField 
     ) t1
     on tbl1.someField = e.field1;

这可能会回来 NULL 值，可以将其转换为 0 如果需要，使用 coalesce() .

赞(0）回复(0）举报 2021-07-29

我来回答

如何为添加到select的子查询返回多个列？

1条答案

相关问题

热门标签

最新问答