oracle中如何对已求出和的字段再求和

发布网友发布时间：2022-04-09 06:55

共1个回答

热心网友时间：2022-04-09 08:24

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
)

请根据需要，具体斟酌追问

我想得到的这样的结果！谢谢！最下面多出一行，对每列求和。

追答你的语句后加上以下语句即可：

UNION ALL
select '总计',
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