Oracle组/计数查询

本文介绍了Oracle组/计数查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下两个表：

TableOne ======== Id1|ColA1|ColB1|ColC1|ColD1|ColE1 -------------------------------- 1| AFoo|BFoo |CFoo | DFoo| EFoo 2| AFoo|BBar |CFoo | DFoo| EFoo TableTwo ======== Id2|ColA2|ColB2|ColC2 --------------------- 11| 1 |ABC |NOP | 12| 1 |ABC |QRS | 13| 1 |DEF |TUV | 14| 1 |DEF |WXY | 15| 1 |DEF |FGH | 16| 2 |ABC |NOP |

我运行以下查询：

select t1.*, t2.* from TableOne t1 inner join TableTwo t2 on t2.ColA2=t1.Id1 where t1.ColA1='AFoo'

并获得以下结果：

Result ====== Id1|ColA1|ColB1|ColC1|ColD1|ColE1|Id2|ColA2|ColB2|ColC2 ------------------------------------------------------- 1| AFoo|BFoo |CFoo | DFoo| EFoo| 11| 1 | ABC | NOP 1| AFoo|BFoo |CFoo | DFoo| EFoo| 12| 1 | ABC | QRS 1| AFoo|BFoo |CFoo | DFoo| EFoo| 13| 1 | DEF | TUV 1| AFoo|BFoo |CFoo | DFoo| EFoo| 14| 1 | DEF | WXY 1| AFoo|BFoo |CFoo | DFoo| EFoo| 15| 1 | DEF | FGH 2| AFoo|BBar |CFoo | DFoo| EFoo| 16| 2 | ABC | NOP

我真正想要返回的是：

Desired Result ====== Id1|MaxDup ---------------------------------------- 1| 3 (This is because there are 3 DEF records) 2| 1 (This is because there is 1 ABC record)

的出现在每个TableOne行的ColB2中。在上面的示例中，ID1为1有两个ABC记录和三个DEF记录与它相关联。因为有比DEF记录更多的DEF记录，我想要返回DEF记录的计数。

So, I am trying to track the maximum number of occurrences in ColB2 which appears for each TableOne row. In the example above, the ID1 of 1 has two ABC records and three DEF records associated with it. Since there are more DEF records than ABC records, I want the count of DEF records returned.

任何人都可以提供一个工作示例来证明这一点？

Can anybody provide a working example that can demonstrate this?

谢谢！

推荐答案

尝试这个（我没有测试过） / p>

Try this one (I haven't tested it):

with t2 as ( select ColA2, ColB2, count(*) cnt from TableTwo group by ColA2, ColB2 ) select t1.Id1, ( select max(cnt) MaxDup from t2 where t2.ColA2=t1.Id1) from TableOne t1