如何选择和排序按列不在Groupy中按SQL语句 - Oracle [英] How to Select and Order By columns not in Groupy By SQL statement - Oracle

问题描述

  SELECT 
 IMPORTID，Region，RefObligor，SUM（NOTIONAL）AS SUM_NOTIONAL 
从
位置
其中
 ID =：importID 
 GROUP BY 
 IMPORTID，Region，RefObligor 
 Order BY 
 IMPORTID，地区，RefObligor 
  

表位置我希望作为显示数据的输出，但我不想在group by语句中使用。

这些是 Site，办公桌

最终输出将包含以下列：

  IMPORTID，Region，Site，Desk，RefObligor，SUM（NOTIONAL）AS SUM_NOTIONAL 
  

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

  Order BY 
 IMPORTID，Region，Site，Desk，RefObligor 
  

如何做到这一点？

解决方案

包含它是没有意义的不属于GROUP BY子句的列。考虑如果你在SELECT子句中有MIN（X），MAX（Y），那么其他列（不分组）来自哪一行呢？ 如果你的Oracle版本已经足够了，您可以使用SUM - OVER（）来针对每个数据行显示SUM（分组）。

  SELECT 
 IMPORTID，Site，Desk，Region，RefObligor，
 SUM（NOTIONAL）OVER（由IMPORTID分区，RefObligor）AS SUM_NOTIONAL 
 From 
 Positions 
 Where 
 ID =：importID 
 Order BY 
 IMPORTID，Region，Site，Desk，RefObligor 
  

或者，您需要从 Site ， Desk 列

 选择
 IMPORTID，地区，最小（站点）站点，最小（台）桌面，RefObligor，SUM（NOTIONAL）AS SUM_NOTIONAL 
从
位置
其中
 ID =：importID 
 GROUP BY 
 IMPORTID，Region，RefObligor 
 Order BY 
 IMPORTID，Region，闵（网站），民（台），RefObligo r 
  

I have the following statement:

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

There exists some extra columns in table Positions that I want as output for "display data" but I don't want in the group by statement.

These are Site, Desk

Final output would have the following columns:

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

Ideally I'd want the data sorted like:

Order BY 
    IMPORTID,Region,Site,Desk,RefObligor

How to achieve this?

解决方案

It does not make sense to include columns that are not part of the GROUP BY clause. Consider if you have a MIN(X), MAX(Y) in the SELECT clause, which row should other columns (not grouped) come from?

If your Oracle version is recent enough, you can use SUM - OVER() to show the SUM (grouped) against every data row.

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

Alternatively, you need to make an aggregate out of the Site, Desk columns

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

这篇关于如何选择和排序按列不在Groupy中按SQL语句 - Oracle的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！