10-251 2段階の集計

SQLパズル

ColTable
ColA  ColB
----  ----
   0  AAAA
   1  BBBB
   1  AAAA
   1  GGGG
   2  CCCC
   3  AAAA
   4  BBBB
   4  EEEE
   5  FFFF

ColAごとで、
その行のColBの出現回数の最大値が1より大きい行を出力する。

出力結果
ColA  ColB
----  ----
   0  AAAA
   1  BBBB
   1  AAAA
   1  GGGG
   3  AAAA
   4  BBBB
   4  EEEE

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table ColTable(ColA,ColB) as
select 0,'AAAA' from dual union all
select 1,'BBBB' from dual union all
select 1,'AAAA' from dual union all
select 1,'GGGG' from dual union all
select 2,'CCCC' from dual union all
select 3,'AAAA' from dual union all
select 4,'BBBB' from dual union all
select 4,'EEEE' from dual union all
select 5,'FFFF' from dual;

SQL

--■■■分析関数を使う方法■■■
select ColA,ColB
from (select ColA,ColB,
      max(cnt) over(partition by ColA) as maxCnt
      from (select ColA,ColB,
            count(*) over(partition by ColB) as cnt
              from ColTable))
where maxCnt >1;

--■■■分析関数を使わない方法■■■
select ColA,ColB
  from ColTable a
 where exists(select 1 from ColTable b
              group by b.ColB
              having count(*) > 1
                 and max(case when b.ColA = a.ColA then 1 else 0 end) = 1)
order by ColA,ColB;

解説

2段階で分析関数を使っています。

分析関数を使わない方法は、面白いSQLですねぇ

---