在单个列上：聚合函数的代数运算(On a single column : Algebraic operation on Aggregate functions)

在单个列上：聚合函数的代数运算(On a single column : Algebraic operation on Aggregate functions)

是否可以在相同的SQL Select Query（无函数/过程和PL / SQL）上计算两个聚合函数的代数运算？

| id || A | |-----||-----| | 1 || 10 | | 2 || 20 | | 3 || 30 | | 4 || 40 | SELECT (SELECT SUM(A) FROM Table Where 'id is even') - (SELECT SUM(A) FROM Table Where 'id is odd') FROM TABLE WHERE 'condition'

任何的想法 ？

ps：我的数据库是Oracle 11g

Is it possible on the same SQL Select Query (no function/procedure nor PL/SQL) to compute an algebraic operation on two aggregate functions ?

| id || A | |-----||-----| | 1 || 10 | | 2 || 20 | | 3 || 30 | | 4 || 40 | SELECT (SELECT SUM(A) FROM Table Where 'id is even') - (SELECT SUM(A) FROM Table Where 'id is odd') FROM TABLE WHERE 'condition'

Any idea ?

ps : My databse is Oracle 11g

最满意答案

你可以这样做：

select sum(case when mod(id,2)=0 then A else 0 end) - sum(case when mod(id,2)=0 then 0 else a end) from test where yourCondition

SQLFiddle是一个MySql示例，因为Oracle也有mod函数，它应该按原样运行。

看到它在这里工作： http ： //sqlfiddle.com/#！9/1ba756/2

You can do it as this:

select sum(case when mod(id,2)=0 then A else 0 end) - sum(case when mod(id,2)=0 then 0 else a end) from test where yourCondition

The SQLFiddle is a MySql sample, since the Oracle also have mod function it should work as is.

See it working here: http://sqlfiddle.com/#!9/1ba756/2