如何选择和排序不在 Groupy By SQL 语句中的列 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4787104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Select and Order By columns not in Groupy By SQL statement - Oracle
提问by m3ntat
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 Positionsthat I want as output for "display data" but I don't want in the group by statement.
表中存在一些额外的列Positions，我想要作为“显示数据”的输出，但我不想在 group by 语句中。
These are Site, Desk
这些是 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?
如何实现这一目标？
回答by RichardTheKiwi
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?
包含不属于 GROUP BY 子句的列是没有意义的。考虑在 SELECT 子句中是否有 MIN(X)、MAX(Y)，其他列（未分组）应该来自哪一行？
If your Oracle version is recent enough, you can use SUM - OVER() to show the SUM (grouped) against every data row.
如果您的 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
Alternatively, you need to make an aggregate out of the Site, Deskcolumns
或者，您需要对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
回答by René Nyffenegger
I believe this is
我相信这是
select
IMPORTID,
Region,
Site,
Desk,
RefObligor,
Sum(Sum(Notional)) over (partition by IMPORTID, Region, RefObligor)
from
Positions
group by
IMPORTID, Region, Site, Desk, RefObligor
order by
IMPORTID, Region, RefObligor, Site, Desk;
... but it's hard to tell without further information and/or test data.
...但如果没有进一步的信息和/或测试数据，就很难判断。
回答by phyatt
A great blog post that covers this dilemma in detail is here:
这里有一篇很棒的博客文章，详细介绍了这个困境：
http://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/
http://bernardoamc.github.io/sql/2015/05/04/group-by-non-aggregate-columns/
Here are some snippets of it:
以下是其中的一些片段：
Given:
鉴于：
CREATE TABLE games ( game_id serial PRIMARY KEY, name VARCHAR, price BIGINT, released_at DATE, publisher TEXT ); INSERT INTO games (name, price, released_at, publisher) VALUES ('Metal Slug Defense', 30, '2015-05-01', 'SNK Playmore'), ('Project Druid', 20, '2015-05-01', 'shortcircuit'), ('Chroma Squad', 40, '2015-04-30', 'Behold Studios'), ('Soul Locus', 30, '2015-04-30', 'Fat Loot Games'), ('Subterrain', 40, '2015-04-30', 'Pixellore'); SELECT * FROM games; game_id | name | price | released_at | publisher ---------+--------------------+-------+-------------+---------------- 1 | Metal Slug Defense | 30 | 2015-05-01 | SNK Playmore 2 | Project Druid | 20 | 2015-05-01 | shortcircuit 3 | Chroma Squad | 40 | 2015-04-30 | Behold Studios 4 | Soul Locus | 30 | 2015-04-30 | Fat Loot Games 5 | Subterrain | 40 | 2015-04-30 | Pixellore (5 rows)
CREATE TABLE games ( game_id serial PRIMARY KEY, name VARCHAR, price BIGINT, released_at DATE, publisher TEXT ); INSERT INTO games (name, price, released_at, publisher) VALUES ('Metal Slug Defense', 30, '2015-05-01', 'SNK Playmore'), ('Project Druid', 20, '2015-05-01', 'shortcircuit'), ('Chroma Squad', 40, '2015-04-30', 'Behold Studios'), ('Soul Locus', 30, '2015-04-30', 'Fat Loot Games'), ('Subterrain', 40, '2015-04-30', 'Pixellore'); SELECT * FROM games; game_id | name | price | released_at | publisher ---------+--------------------+-------+-------------+---------------- 1 | Metal Slug Defense | 30 | 2015-05-01 | SNK Playmore 2 | Project Druid | 20 | 2015-05-01 | shortcircuit 3 | Chroma Squad | 40 | 2015-04-30 | Behold Studios 4 | Soul Locus | 30 | 2015-04-30 | Fat Loot Games 5 | Subterrain | 40 | 2015-04-30 | Pixellore (5 rows)
Trying to get something like this:
试图得到这样的东西：
SELECT released_at, name, publisher, MAX(price) as most_expensive FROM games GROUP BY released_at;
SELECT released_at, name, publisher, MAX(price) as most_expensive FROM games GROUP BY released_at;
But nameand publisherare not added due to being ambiguous when aggregating...
但是name，publisher由于聚合时不明确而没有添加......
Let's make this clear:
Selecting the MAX(price) does not select the entire row.The database can't know and when it can't give the right answer every time for a given query it should give us an error, and that's what it does!
Ok… Ok… It's not so simple, what can we do?
让我们说清楚：
Selecting the MAX(price) does not select the entire row.数据库无法知道，当它不能每次都为给定查询提供正确答案时，它应该给我们一个错误，这就是它所做的！
好吧……好吧……没那么简单，怎么办？
Use an
inner jointo get the additional columnsSELECT g1.name, g1.publisher, g1.price, g1.released_at FROM games AS g1 INNER JOIN ( SELECT released_at, MAX(price) as price FROM games GROUP BY released_at ) AS g2 ON g2.released_at = g1.released_at AND g2.price = g1.price;Or Use a
left outer jointo get the additional columns, and then filter by the NULL of a duplicate column...SELECT g1.name, g1.publisher, g1.price, g2.price, g1.released_at FROM games AS g1 LEFT OUTER JOIN games AS g2 ON g1.released_at = g2.released_at AND g1.price < g2.price WHERE g2.price IS NULL;
使用 an
inner join获取附加列SELECT g1.name, g1.publisher, g1.price, g1.released_at FROM games AS g1 INNER JOIN ( SELECT released_at, MAX(price) as price FROM games GROUP BY released_at ) AS g2 ON g2.released_at = g1.released_at AND g2.price = g1.price;或使用 a
left outer join获取附加列，然后按重复列的 NULL 过滤...SELECT g1.name, g1.publisher, g1.price, g2.price, g1.released_at FROM games AS g1 LEFT OUTER JOIN games AS g2 ON g1.released_at = g2.released_at AND g1.price < g2.price WHERE g2.price IS NULL;
Hope that helps.
希望有帮助。
