如何根据另一个查询向现有查询结果添加2列?

mfpqipee  于 2021-08-09  发布在  Java
关注(0)|答案(4)|浏览(290)

我有这样一个问题:

SELECT 
   field1 as field1 , 
   field2 as field2 , 
   (select count(*) from ... where ...=field1) as field3
FROM
 ...

它工作得很好-我在结果中看到了3列
我需要为内部查询再添加一列:

SELECT 
       field1 as field1 , 
       field2 as field2 , 
       (select count(*) as my_count, sum(*) as my _sum from ...where ...=field1 ) as field3
    FROM
     ...

这种语法不起作用。
我怎样才能做到呢?

oipij1gg

oipij1gg1#

以下内容似乎可以满足您的需要,在oracle 9i中应该可以正常工作:

SELECT t.field1, 
       t.field2,
       x.my_count,
       x.my_sum
  FROM SOME_TABLE t
  LEFT OUTER JOIN (select FIELD1,
                          count(*) as my_count,
                          sum(SOME_FIELD) as my_sum
                     from SOME_OTHER_TABLE
                     GROUP BY FIELD1) x
    ON x.FIELD1 = t.FIELD1
ozxc1zmp

ozxc1zmp2#

这个部分查询不确定您真正想要的是什么,但是我希望子查询实际上与外部查询相关(否则,您可以 cross join ). 如果是这样,典型的解决方案是横向连接。
博士后:

select 
   field1 as field1, 
   field2 as field2, 
   x.*
from ...
left join lateral (
     select count(*) as my_count, sum(*) as my _sum from ... 
) x

oracle支持从版本12开始的横向联接。你只需要替换 left join lateralouter apply .

exdqitrt

exdqitrt3#

可以使用cte(公共表表达式)预计算值:

WITH
q as (select count(*) as my_count, sum(*) as my _sum from ... )
SELECT 
       field1 as field1 , 
       field2 as field2 , 
       q.my_count as field3, 
       q.my_sum as field4
FROM
...
CROSS JOIN q

或者。。。您可以始终使用性能更低、更简单的方法:

SELECT 
       field1 as field1 , 
       field2 as field2 , 
       (select count(*) from ... ) as field3,
       (select sum(*) from ... ) as field4
    FROM
     ...
xfyts7mz

xfyts7mz4#

由于您有限的(有点混乱的-2数据库,sum(*)…)信息,
逻辑如下:

SELECT 
       field1 as field1 , 
       field2 as field2 , 
       (select count(*) from ... ) as my_count,
       (Select sum(<my field>) from ...) as my _sum 
    FROM
     ...

相关问题