sql - 选择 SUM 作为字段

Question

假设我有这张 table

表(a、b、c、d)。数据类型并不重要。

我想做这个

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

...但我找不到方法(sqldeveloper 一直提示“找不到 from 子句”。)

有办法吗？是否可以？

Answer 1

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

将其重写为这样更简单有效:

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

请注意 SUM(SUM(d))这里。

最里面 SUM是聚合函数。它计算 SUM(d) a-b-c -明智的。

最外层 SUM是解析函数。它总结了预先计算的 SUM(d)的 a-b -wise，并与每一行一起返回值。