8-1 同一グループ件数と、最小かを取得

SQLパズル

マスタテーブル
分類Code   補助分類Code
--------   ------------
    001            01
    001            02
    001            03
    001            04
    002            01
    002            02
    003            99
    004            05
    005            77

マスタテーブルから、
同一の分類Code、補助分類Codeのレコード数と、
同一の分類Code、補助分類Codeの中で、最小の補助分類Codeなら1そうでなければ0、
を出力する。

出力結果
分類Code   補助分類Code  同一グループ件数  IsMin
--------   ------------  --------------  ------
    001            01                4       1
    001            02                4       0
    001            03                4       0
    001            04                4       0
    002            01                2       1
    002            02                2       0
    003            99                1       1
    004            05                1       1
    005            77                1       1

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

データ作成スクリプト

create table マスタテーブル(
分類Code     char(3),
補助分類Code char(2));

insert into マスタテーブル values('001','01');
insert into マスタテーブル values('001','02');
insert into マスタテーブル values('001','03');
insert into マスタテーブル values('001','04');
insert into マスタテーブル values('002','01');
insert into マスタテーブル values('002','02');
insert into マスタテーブル values('003','99');
insert into マスタテーブル values('004','05');
insert into マスタテーブル values('005','77');
commit;

SQL

--■■■分析関数とcase式を使用■■■
select 分類Code,補助分類Code,
count(分類Code) over(partition by 分類Code) as 同一グループ件数,
case when 補助分類Code = min(補助分類Code) over(partition by 分類Code)
then 1 else 0 end as IsMin
from マスタテーブル
order by 分類Code,補助分類Code;

--■■■分析関数とdecode関数を使用■■■
select 分類Code,補助分類Code,
count(分類Code) over(partition by 分類Code) as 同一グループ件数,
decode(補助分類Code,min(補助分類Code) over(partition by 分類Code),1,0) as IsMin
from マスタテーブル
order by 分類Code,補助分類Code;

--■■■相関サブクエリを使用■■■
select 分類Code,補助分類Code,
(select count(b.分類Code) from マスタテーブル b
  where b.分類Code = a.分類Code) as 同一グループ件数,
case when 補助分類Code = (select min(b.補助分類Code) from  マスタテーブル b
                            where b.分類Code = a.分類Code)
then 1 else 0 end as IsMin
from マスタテーブル a
order by 分類Code,補助分類Code;

--■■■相関サブクエリを使用■■■
select 分類Code,補助分類Code,
(select count(b.分類Code) from マスタテーブル b
  where b.分類Code = a.分類Code) as 同一グループ件数,
case when exists(select 1 from マスタテーブル b
                  where b.分類Code = a.分類Code
                    and b.補助分類Code < a.補助分類Code)
then 0 else 1 end as IsMin
from マスタテーブル a
order by 分類Code,補助分類Code;

解説

自己結合と相関サブクエリは、分析関数で代用できます。

---