ORACLE 中多列的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14749059/
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
sum of multiple columns in ORACLE
提问by user2050018
I have a table that has 97 columns, I want to sum 96 columns.
我有一个有 97 列的表，我想对 96 列求和。
select sum(col1+col2+col3+.....+col96)
from tableA where meter_id=x;
I do not want to give all 96 column names, what is the best way to do it? Regards, RR
我不想给出所有 96 个列名，最好的方法是什么？问候， RR
回答by APC
There is no way to avoid writing each column name. All you can do is curse the stupid data modeller and get busy with cut'n'paste.
没有办法避免写每个列名。您所能做的就是诅咒愚蠢的数据建模者并忙于剪切和粘贴。
回答by Mike Meyers
In the case where there are a significant number of columns, I would look at using the data dictionary tables to help create the query by using a query like the one below:
在有大量列的情况下，我会考虑使用数据字典表通过使用如下查询来帮助创建查询：
Select column_name || '+' as column_name_list
From user_tab_columns
Where table_name = 'TABLEA'
Order by column_id
It doesn't change the world but does simplify writing one query.
它不会改变世界，但确实简化了编写一个查询。
回答by Steve
You might be best to sum the columns and then put the result in Excel to do the sum of sums. Otherwise this query should do what you need:
您可能最好对列求和，然后将结果放入 Excel 中以求总和。否则这个查询应该做你需要的：
SELECT SUM(TOTAL_SUM) FROM (
SELECT SUM(column1) AS TOTAL_SUM FROM your_table
UNION
SELECT SUM(column2) AS TOTAL_SUM FROM your_table
UNION
SELECT SUM(column3) AS TOTAL_SUM FROM your_table
);
回答by GriffeyDog
You could create a virtual columnthat adds up your 96 columns, something like:
您可以创建一个将 96 列相加的虚拟列，例如：
alter table TABLEA add (MY_TOTAL_COL NUMBER GENERATED ALWAYS AS (col1+col2+col3...) VIRTUAL);
Then your query can simply do sum(my_total_col).
那么您的查询就可以简单地做sum(my_total_col)。
回答by Lynn Maya
SELECT A.consol_key,
A.amt_lcy,
B.amt_lcy,
C.amt_lcy
FROM categ A,
spec B,
stmt C;
SELECT Sum(total_sum)
FROM (SELECT Sum(amt_lcy) AS TOTAL_SUM
FROM categ
UNION
SELECT Sum(amt_lcy) AS TOTAL_SUM
FROM spec
UNION
SELECT Sum(amt_lcy) AS TOTAL_SUM
FROM stmt)
WHERE table_id NOT IN (SELECT table_id
FROM categ
WHERE txn_code = 'COR'
AND system_id <> 'AA');
回答by Turo
It could be possible:
这可能是可能的：
Using Can an SQL procedure return a table?and the answer of Mike Meyers you could write a stored procedure using dynamic sql
使用SQL 过程能否返回表？以及 Mike Meyers 的回答，您可以使用动态 sql 编写存储过程
sumcolumns(columnfilter,tablename,whereclause)
and use it something like
并使用它像
select *
from table(sumcolumns('column_name <> ''col97''','tableA','meter_id=x'))
回答by Roger Cornejo
Try using UNPIVOT as per example below (still need to specify the column list as others have noted):
尝试按照下面的示例使用 UNPIVOT（仍然需要像其他人指出的那样指定列列表）：
with tableA as /* prototype tableA just for example */
(
select 1 meter_id, 101 col1, 10 col2, 20 col3, 30 col4, NULL col5, 101 col11, 10 col12, 20 col13, 30 col14, NULL col15, 101 col21, 10 col22, 20 col23, 30 col24, NULL col25 from dual union
select 2, 102, 40, NULL, 50, NULL, 102, 40, NULL, 50, NULL, 102, 40, NULL, 50, NULL from dual union
select 3, 103, 60, 70, 80, 90, 103, 60, 70, 80, 90, 103, 60, 70, 80, 90 from dual union
select 4, 104, 100, NULL, NULL, NULL, 104, 100, NULL, NULL, NULL, 104, 100, NULL, NULL, NULL from dual
)
, unpivoted_tableA as /* UNPIVOT tableA columns into rows */
(
select *
from tableA
unpivot include nulls
(
col_value for col_ in
(COL1,COL2,COL3,COL4,COL5,COL11,COL12,COL13,COL14,COL15,COL21,COL22,COL23,COL24,COL25)
)
)
/* main query - Sum of all columns that were unpivoted to rows */
select meter_id, sum(col_value)
from unpivoted_tableA
group by meter_id
;
