oracle 选择 SUM 作为字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1538341/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SELECT SUM as field
提问by Tom
Suppose i have this table
假设我有这张桌子
table (a,b,c,d). Datatypes are not important.
表（a、b、c、d）。数据类型并不重要。
I want to do this
我想做这个
select a as a1,b as b1,c as c1,
(select sum(d) from table where a=a1 and b=b1) as total
from table
group by a,b,c
...but I can't find a way (sqldeveloper keeps complaining with "from clause not found".)
...但我找不到方法（sqldeveloper 一直抱怨“找不到 from 子句”。）
Is there a way? Is it possible?
有办法吗？是否可以？
回答by Quassnoi
SELECT a as a1,b as b1,c as c1,
(
SELECT SUM(d)
FROM mytable mi
WHERE mi.a = mo.a
AND mi.b= mo.b
) as total
FROM mytable mo
GROUP BY
a, b, c
It's much more simple and efficient to rewrite it as this:
将其重写为这样更简单有效：
SELECT a AS a1, B AS b1, c AS c1, SUM(SUM(d)) OVER (PARTITION BY a, b) AS total
FROM mytable
GROUP BY
a, b, c
Note the SUM(SUM(d))here.
注意SUM(SUM(d))这里。
The innermost SUMis the aggregate function. It calculates the SUM(d)a-b-c-wise.
最里面的SUM是聚合函数。它计算SUM(d)a-b-c明智的。
The outermost SUMis the analytic function. It sums the precalculated SUM(d)'s a-b-wise, and returns the value along with each row.
最外层SUM是解析函数。它对预先计算的SUM(d)'sa-b求和，并与每一行一起返回值。
回答by Maximilian Mayerl
Du you mean something like this?
杜你是这个意思吗？
select a as a1,
b as b1,
c as c1,
sum(sum(d)) OVER (PARTITION BY a, b) AS total
from table
group by a,b,c
回答by Vincent Malgrat
you can do it with aliases:
你可以用别名来做到这一点：
SELECT a AS a1, b AS b1, c AS c1,
(SELECT SUM(d)
FROM test_t t_in
WHERE t_in.a = t.a
AND t_in.b = t.b) AS total
FROM test_t t
GROUP BY a, b, c
