ORACLE选择并分组以排除一个字段 [英] ORACLE Select and group by excluding one field

问题描述

我有一个非常简单的查询(在Oracle 11g上)以选择3个字段:

I have a very simple query (on Oracle 11g) to select 3 fields:

select field1, field2, field3, count(*) from table
where...
group by field1, field2, field3
having count(*) > 10;

现在，我需要从"group by"中排除"field3"，因为我只需要对字段1和2进行分组，但在输出中也需要field3. 据我所知，选择中的所有字段也必须在分组依据"中报告，那么我该如何处理呢?

Now, what I need, is exclude "field3" from the "group by" since I only need field 1 and 2 to be grouped, but I also need field3 in the output. As far I know, all the fields in the select must be reported also in "group by", so how can I handle that?

谢谢 卢卡斯

推荐答案

select t.field1, t.field2, t.field3, tc.Count
from table t
inner join (
    select field1, field2, count(*) as Count
    from table 
    where... 
    group by field1, field2 
    having count(*) > 10 
) tc on t.field1 = tc.field1 and t.field2 = tc.field2

这篇关于ORACLE选择并分组以排除一个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！