本文介绍了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更多推荐
Oracle组/计数查询
发布评论