7-61 入室回数の降順に出力

SQLパズル

入室履歴テーブル
入室者      部屋
--------    ----------
ミレニア    祭壇の部屋
ミレニア    断崖の部屋
ミレニア    投獄の部屋
レイナ      業火の部屋
レイナ      受刑の部屋
アリシア    業火の部屋
アリシア    砂岩の部屋
アリシア    祭壇の部屋
アリシア    断崖の部屋

入室者ごとの入室回数の降順、
部屋の降順に出力する。

出力結果
入室者      部屋
--------    ----------
アリシア    業火の部屋
アリシア    砂岩の部屋
アリシア    祭壇の部屋
アリシア    断崖の部屋
ミレニア    祭壇の部屋
ミレニア    断崖の部屋
ミレニア    投獄の部屋
レイナ      業火の部屋
レイナ      受刑の部屋

データ作成スクリプト

create table 入室履歴(
入室者 varchar2(8),
部屋   char(10));

insert into 入室履歴 values('ミレニア','祭壇の部屋');
insert into 入室履歴 values('ミレニア','断崖の部屋');
insert into 入室履歴 values('ミレニア','投獄の部屋');
insert into 入室履歴 values('レイナ'  ,'業火の部屋');
insert into 入室履歴 values('レイナ'  ,'受刑の部屋');
insert into 入室履歴 values('アリシア','業火の部屋');
insert into 入室履歴 values('アリシア','砂岩の部屋');
insert into 入室履歴 values('アリシア','祭壇の部屋');
insert into 入室履歴 values('アリシア','断崖の部屋');
commit;

SQL

--■■■分析関数を使う方法■■■
select 入室者,部屋
  from 入室履歴
order by count(*) over(partition by 入室者) desc,部屋;

--■■■相関サブクエリを使う方法■■■
select 入室者,部屋
  from 入室履歴 a
order by (select count(*) from 入室履歴 b
           where b.入室者 = a.入室者) desc,部屋;

解説

order by句で、分析関数を使うことができます。

---