总和不同于其他列 [英] Sum Distinct By Other Column
问题描述
我有一个PL / SQL的问题，因为我是在PL / SQL世界的新。
假设我有这样的表格。
COlumnA COlumnB COlumnC
1 5000000000 X
1 5000000000 X
2 4350000000 X
2 4350000000 X
3 10000000000 X
3 10000000000 X
3 10000000000 X
4 1809469720 Y
5 10000000000 X
5 10000000000 X
6 3000000000 X
6 3000000000 X
我想要产生如下的select语句。
ColumnC | Sum
X | 32350000000
Y | 1809469720
我在内部查询中解决了Oracle 12c中的这个问题，但是当系统需要转到Oracle 11g，我的查询不工作了，我需要只有一个select语句的预期结果。
任何人都可以告诉？
谢谢！
这是我想出来的...
SELECT d.columnc ASColumnC
， SUM（d.columnb）ASSum
FROM（SELECT t.columna
，t.columnb
，t.columnc
FROM tablea t
GROUP
BY t.columna
，t.columnb
，t.columnc
）d
GROUP
BY d.columnc
这使用内联视图（别名为d）从tablea返回一组不同的行。我们可以使用GROUP BY子句或者包括DISTINCT关键字，甚至通过编写使用UNION集合运算符的查询来获取不同的集合。
只需在括号中包装该查询，分配别名，并在FROM子句中使用它，就好像它是一个表或视图。
该语句的操作方式类似于在FROM子句中引用VIEW。
您不需要这样做，查询上面操作。我们可以创建一个视图，如下所示：
CREATE VIEW d AS
SELECT t.columna
， t.columnb
，t.columnc
FROM tablea t
GROUP
BY t.columna
，t.columnb
，t.columnc
然后我们可以引用另一个查询的FROM子句中的视图，例如
SELECT d.columnc ASColumnC
，SUM（d.columnb）ASSum
FROM d
GROUP
BY d.columnc
但我们实际上不需要创建VIEW对象。我们可以将视图查询作为内联视图。
我不相信Oracle 11g对内嵌视图的嵌套有三个层次的限制。我怀疑你正在运行的限制与相关的子查询相关。子查询可以引用来自外部查询的列，但只能使用它所使用的查询中的最多一级...列。它不能引用更远的查询中的列。 （我没有通过测试确认，但这是我的回忆。）
这是Oracle的实际ORA-和/或PLS-错误消息的一些有助于识别您遇到的限制。
I have a problem with PL/SQL since i am new in PL/SQL world. Let's say i have table like this.
COlumnA COlumnB COlumnC
1 5000000000 X
1 5000000000 X
2 4350000000 X
2 4350000000 X
3 10000000000 X
3 10000000000 X
3 10000000000 X
4 1809469720 Y
5 10000000000 X
5 10000000000 X
6 3000000000 X
6 3000000000 X
And i want to produce select statement as below.
ColumnC |Sum
X |32350000000
Y |1809469720
I have solved this problem in Oracle 12c with inner query, but when the system need to go to Oracle 11g, my query doesn't work anymore, i need to have the expected result with only one select statement.
Could anyone please advise?
Thank you!
This is what I came up with... using an inline view rather than a correlated subquery in the SELECT list.
SELECT d.columnc AS "ColumnC"
, SUM(d.columnb) AS "Sum"
FROM ( SELECT t.columna
, t.columnb
, t.columnc
FROM tablea t
GROUP
BY t.columna
, t.columnb
, t.columnc
) d
GROUP
BY d.columnc
This uses an inline view (aliased as "d") to return a "distinct" set of rows from tablea. We can get a distinct set, using a GROUP BY clause, or including the DISTINCT keyword, or even by writing a query that uses a UNION set operator.
Just wrap that query in parens, assign an alias, and use it in the FROM clause, as if it were a table or view.
The statement operates similarly to referencing a VIEW in the FROM clause.
You don't need to do this, but to illustrate how the query above operates. We could create a view, like this:
CREATE VIEW d AS
SELECT t.columna
, t.columnb
, t.columnc
FROM tablea t
GROUP
BY t.columna
, t.columnb
, t.columnc
And then we can reference the view in the FROM clause of another query, for example
SELECT d.columnc AS "ColumnC"
, SUM(d.columnb) AS "Sum"
FROM d
GROUP
BY d.columnc
But we don't actually need to create the VIEW object. We can include the view query as an "inline view".
I don't believe that Oracle 11g has a restriction on the nesting of inline views to three levels. I suspect that the restriction you are running into is related to correlated subqueries. The subquery can reference columns from the outer query, but only up one level... columns from the query it's used in. It can't reference columns in a query that is further out. (I've not confirmed with testing, but that's my recollection.)
This is where the actual ORA- and/or PLS- error message from Oracle would be of some help in identifying the restriction you are running into.
这篇关于总和不同于其他列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
