sql - 如何选择和排序不在 Group By SQL 语句中的列 - Oracle

Question

我有如下声明:

SELECT  
    IMPORTID,Region,RefObligor,SUM(NOTIONAL) AS SUM_NOTIONAL
From 
    Positions
Where
    ID = :importID
GROUP BY 
    IMPORTID, Region,RefObligor
Order BY 
    IMPORTID, Region,RefObligor

Positions 表中有一些额外的列，我希望这些列作为“显示数据”的输出，但我不想在 group by 语句中。

这些是站点、办公 table

最终输出将包含以下列:

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

理想情况下，我希望数据排序如下:

Order BY 
    IMPORTID,Region,Site,Desk,RefObligor

如何做到这一点？

Answer 1

包含不属于 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