oracle按非选列排序,如何选择和排序不在Groupy中的列按SQL语句 – Oracle

包含不属于GROUP BY子句的列是没有意义的.考虑一下SELECT子句中是否有MIN(X),MAX(Y),其他列(未分组)应该来自哪一行？

如果您的Oracle版本足够新,则可以使用SUM-OVER()来显示针对每个数据行的SUM(分组).

SELECT

IMPORTID,Site,Desk,Region,RefObligor,

SUM(NOTIONAL) OVER(PARTITION BY IMPORTID, Region,RefObligor) AS SUM_NOTIONAL

From

Positions

Where

ID = :importID

Order BY

IMPORTID,Region,Site,Desk,RefObligor

或者,您需要从Site,Desk列中进行聚合

SELECT

IMPORTID,Region,Min(Site) Site, Min(Desk) Desk,RefObligor,SUM(NOTIONAL) AS SUM_NOTIONAL

From

Positions

Where

ID = :importID

GROUP BY

IMPORTID, Region,RefObligor

Order BY

IMPORTID, Region,Min(Site),Min(Desk),RefObligor