oracle oracle中的group by和union
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30712302/
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
group by and union in oracle
提问by eshaa
I would like to union 2 queries but facing an error in oracle.
我想联合 2 个查询，但在 oracle 中遇到错误。
select count(*) as faultCount,
COMP_IDENTIFIER
from CORDYS_NCB_LOG
where AUDIT_CONTEXT='FAULT'
union
select count(*) as responseCount,
COMP_IDENTIFIER
from CORDYS_NCB_LOG
where AUDIT_CONTEXT='RESPONSE'
group by COMP_IDENTIFIER
order by responseCount;
Two queries run perfectly individually.but when using union,it says ORA-00904: "RESPONSECOUNT": invalid identifier
两个查询单独运行完美。但是当使用联合时，它说 ORA-00904: "RESPONSECOUNT": invalid identifier
回答by Lukas Eder
The error you've run into
你遇到的错误
In Oracle, it's best to always name each column in each UNIONsubquery the same way. In your case, the following should work:
在 Oracle 中，最好始终以UNION相同的方式命名每个子查询中的每一列。在您的情况下，以下应该有效：
select count(*) as theCount,
COMP_IDENTIFIER
from CORDYS_NCB_LOG
where AUDIT_CONTEXT='FAULT'
group by COMP_IDENTIFIER -- don't forget this
union
select count(*) as theCount,
COMP_IDENTIFIER
from CORDYS_NCB_LOG
where AUDIT_CONTEXT='RESPONSE'
group by COMP_IDENTIFIER
order by theCount;
See also:
也可以看看：
Curious issue with Oracle UNION and ORDER BY
A good workaround is, of course, to use indexed column references as suggested by a_horse_with_no_name
当然，一个好的解决方法是使用a_horse_with_no_name建议的索引列引用
The query you really wanted
您真正想要的查询
From your comments, however, I suspect you wanted to write an entirely different query, namely:
但是，根据您的评论，我怀疑您想编写一个完全不同的查询，即：
select count(case AUDIT_CONTEXT when 'FAULT' then 1 end) as faultCount,
count(case AUDIT_CONTEXT when 'RESPONSE' then 1 end) as responseCount,
COMP_IDENTIFIER
from CORDYS_NCB_LOG
where AUDIT_CONTEXT in ('FAULT', 'RESPONSE')
group by COMP_IDENTIFIER
order by responseCount;
回答by a_horse_with_no_name
The column names of a union are determined by the firstquery. So your first column is actually named FAULTCOUNT.
联合的列名由第一个查询确定。所以你的第一列实际上被命名为FAULTCOUNT.
But the easiest way to sort the result of a union is to use the column index:
但对联合结果进行排序的最简单方法是使用列索引：
select ...
union
select ...
order by 1;
You most probably also want to use UNION ALLwhich avoids removing duplicates between the two queries and is faster than a plain UNION
您很可能还想使用UNION ALL它避免删除两个查询之间的重复项并且比普通查询更快UNION
回答by sujata
In Union or Union all query column names are determined by the first query column name.
在 Union 或 Union 中，所有查询列名都由第一个查询列名决定。
In your query replace "order by responseCount" with "order by faultCount.
在您的查询中，将“order by responseCount”替换为“order by faultCount。
