正文
oracle如何再求和 oracle求和函数
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
oracle 求和函数
连续求和分析函数 sum(…) over(…)
NC示例：
1、select bdcode,sum(1) over(order by bdcode) aa from bd_bdinfo
【示例】
原表信息
规则
sum(…) over( )，对所有行求和。
sum(…) over( order by … )， 连续求和。
sum(…) over( partition by… )，同组内所行求和。
sum(…) over( partition by… order by … )，同第1点中的排序求和原理，只是范围限制在组内。
扩展资料
oracle 数据库的特点
1、完整的数据管理功能：
1）数据的大量性
2）数据的保存的持久性
3）数据的共享性
4）数据的可靠性
2、完备关系的产品：
1）信息准则---关系型DBMS的所有信息都应在逻辑上用一种方法，即表中的值显式地表示；
2）保证访问的准则
3）视图更新准则---只要形成视图的表中的数据变化了，相应的视图中的数据同时变化
4）数据物理性和逻辑性独立准则
3、分布式处理功能：
ORACLE数据库自第5版起就提供了分布式处理能力，到第7版就有比较完善的分布式数据库功能了，一个ORACLE分布式数据库由oraclerdbms、sql*Net、SQL*CONNECT和其他非ORACLE的关系型产品构成。
参考资料来源：百度百科-Oracle数据库
相关问答
Q1: oracle中如何对已求出和的字段再求和
1. 如果，要对每一个 A.LOTID 都计算一次各类的汇总值的话，只需在select A.LOTID,后再加一子句即可，加后为：
select A.LOTID,
SUM(CASE WHEN A.LINE IN ('BML','BLU','GRN','RED','RPL','OCL','IAL','IBL','ANL','PSL','FIL'） THEN A.TIMES ELSE 0 END) AS ALL_CLASS,
SUM(DECODE(A.LINE, 'BML', A.TIMES)) AS BML,
SUM(DECODE(A.LINE, 'BLU', A.TIMES)) AS BLU,
SUM(DECODE(A.LINE, 'GRN', A.TIMES)) AS GRN,
SUM(DECODE(A.LINE, 'RED', A.TIMES)) AS RED,
SUM(DECODE(A.LINE, 'RPL', A.TIMES)) AS RPL,
SUM(DECODE(A.LINE, 'OCL', A.TIMES)) AS OCL,
SUM(DECODE(A.LINE, 'IAL', A.TIMES, 'IBL', A.TIMES)) AS ITO,
SUM(DECODE(A.LINE, 'ANL', A.TIMES)) AS ANL,
SUM(DECODE(A.LINE, 'PSL', A.TIMES)) AS PSL,
SUM(DECODE(A.LINE, 'FIL', A.TIMES)) AS FIL
from (select t.oper_id AS LINE,
substr(t.lot_id, 1, 3) as LOTID,
(to_date(t.end_timestamp, 'yyyy-mm-dd hh24:mi:ss') -
to_date(t.start_timestamp, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 AS TIMES
from report.st_abnormal t
where error_second_code in ('N002', 'B001', 'B002')
and t.txn_date = '2014-03-01'
and t.txn_date = '2014-03-31') A
GROUP BY A.LOTID
2. 如果要对所有的 A.LOTID计算所有类别的汇总的话，可用下面语句（较好）：
select SUM(CASE WHEN A.LINE IN ('BML','BLU','GRN','RED','RPL','OCL','IAL','IBL','ANL','PSL','FIL'） THEN A.TIMES ELSE 0 END) AS ALL_SUM
from (select t.oper_id AS LINE,
substr(t.lot_id, 1, 3) as LOTID,
(to_date(t.end_timestamp, 'yyyy-mm-dd hh24:mi:ss') -
to_date(t.start_timestamp, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 AS TIMES
from report.st_abnormal t
where error_second_code in ('N002', 'B001', 'B002')
and t.txn_date = '2014-03-01'
and t.txn_date = '2014-03-31') A
也可在你的语句外再套一层（此法效率不高，不建议）：
select sum(BML+BLU+GRN+RED+RPL+OCL+ITO+ANL+PSL+FIL) as all_sum
from
（
select A.LOTID,
SUM(DECODE(A.LINE, 'BML', A.TIMES)) AS BML,
SUM(DECODE(A.LINE, 'BLU', A.TIMES)) AS BLU,
SUM(DECODE(A.LINE, 'GRN', A.TIMES)) AS GRN,
SUM(DECODE(A.LINE, 'RED', A.TIMES)) AS RED,
SUM(DECODE(A.LINE, 'RPL', A.TIMES)) AS RPL,
SUM(DECODE(A.LINE, 'OCL', A.TIMES)) AS OCL,
SUM(DECODE(A.LINE, 'IAL', A.TIMES, 'IBL', A.TIMES)) AS ITO,
SUM(DECODE(A.LINE, 'ANL', A.TIMES)) AS ANL,
SUM(DECODE(A.LINE, 'PSL', A.TIMES)) AS PSL,
SUM(DECODE(A.LINE, 'FIL', A.TIMES)) AS FIL
from (select t.oper_id AS LINE,
substr(t.lot_id, 1, 3) as LOTID,
(to_date(t.end_timestamp, 'yyyy-mm-dd hh24:mi:ss') -
to_date(t.start_timestamp, 'yyyy-mm-dd hh24:mi:ss')) * 24 * 60 AS TIMES
from report.st_abnormal t
where error_second_code in ('N002', 'B001', 'B002')
and t.txn_date = '2014-03-01'
and t.txn_date = '2014-03-31') A
GROUP BY A.LOTID
)
请根据需要，具体斟酌
Q2: oracle先分组再求和
1、最简单的方法，使用 UNION
SELECT '苹果' AS NAME, SUM(WEIGHT) AS WEIGHT FROM ZZZZ_TTTT WHERE NAME LIKE '%苹果'
UNION ALL
SELECT '梨' AS NAME, SUM(WEIGHT) AS WEIGHT FROM ZZZZ_TTTT WHERE NAME LIKE '%梨'
2、使用 CASE WHEN
SELECT NAME, SUM(WEIGHT)
FROM (SELECT CASE
WHEN INSTR(NAME, '苹果') 0 THEN
'苹果'
ELSE
'梨'
END AS NAME,
WEIGHT
FROM ZZZZ_TTTT)
GROUP BY NAME
* ZZZZ_TTTT 为表名
Q3: oracle查询并求和
各车间不同工资之和 怎么理解？
(1)不同工资只计算一次
select A,sum(D) from
（
select distinct A，C，D from X
）
group by A;
(2)不同工资按工资级别求和
select A,C,sum(D) from X group by A,C ;
