【MySQL】外部キーが重複しないように１件取得したい

Question

#やってみたいこと

ID(PK)、ニックネームID(外部キー)、優先順位　という３つのカラムを持つテーブルがあるとします。
この時、実際のデータ構造の例として以下のようになっています。

ID	ニックネームID	優先順位
1	20	1
2	20	2
3	20	3
4	21	2
5	21	3
6	23	2
7	24	1
8	24	1
9	24	4

　

優先順位は重複して入る可能性も有りますし、必ず連番になるわけではありません。
この時、以下のようにニックネームIDは重複しないように、かつ優先順位が一番高い(値が小さいもの)を取得したいと考えています。

ID	ニックネームID	優先順位
1	20	1
4	21	2
6	23	2
7	24	1

#現時点で考えている実現方法
取得方法として今考えているのが、

１ ニックネームID順にORDER BY
２ 優先順位順にORDER BY
３ ニックネームIDでDISTINCT

で取得なのですが、
SQLの書き方としてもっと良い方法はないかご相談させていただきたい次第です。

自分の中ではこれが一番いいのでは？　と思い考えたのですが、当方SQL初心者の為、
この方法では実現できないや、あるいはもっと正しい書き方があるという場合、ご指摘いただけますと幸いです。

何卒宜しくお願い致します。

Answer 1

優先順位は重複して入る可能性も有りますし

そもそも、これは意図的なものなのでしょうか。

同じニックネームに同じ優先順位を付ける必要性がないなら、ニックネームIDと優先順位で複合ユニークキーをかけることで、ニックネームIDと優先順位のペアを一意にすることができます。

Comments

[shakevo]

ありがとうございます。それは意図的なものです。
しかし、そのような方法もあるということで勉強になります。
ご回答いただきありがとうございます。

Answer 2

標準的なSQL

ニックネームIDは重複しないように、かつ優先順位が一番高い(値が小さいもの)を取得したい

select ニックネームID, min(優先順位) from <ニックネームテーブル> group by ニックネームID;

ID	ニックネームID	優先順位

IDを複数表示
(ニックネームID, 優先順位)が複数ある場合、IDを複数表示するには、サブクエリを使います。
select ID,ニックネームID, 優先順位 from <ニックネームテーブル> where (ニックネームID, 優先順位) in (select ニックネームID, min(優先順位) from <ニックネームテーブル> group by ニックネームID) order by ニックネームID;

IDを一つだけ表示
(ニックネームID, 優先順位)が複数ある場合、IDを一つだけ表示するには、強引にmin(ID)をとります。
select min(ID),ニックネームID, 優先順位 from <ニックネームテーブル> where (ニックネームID, 優先順位) in (select ニックネームID, min(優先順位) from <ニックネームテーブル> group by ニックネームID) group by ニックネームID, 優先順位;

ここまではMariaDBで試しました。どのデータベースでも動作すると思います。

rank(), row_number()が使えるSQL

IDを複数表示
(ニックネームID, 優先順位)が複数ある場合、IDを複数表示するには、rank()を使います。
select s.ID, s.ニックネームID, s.優先順位 from (select ID,ニックネームID,優先順位,rank() over (partition by ニックネームID order by ニックネームID, 優先順位) from <ニックネームテーブル>)s where s.rank = 1;

IDを一つだけ表示
(ニックネームID, 優先順位)が複数ある場合、IDを一つだけ表示するには、row_number()を使います。
select s.ID, s.ニックネームID, s.優先順位 from (select ID,ニックネームID,優先順位,row_number() over (partition by ニックネームID order by ニックネームID, 優先順位) from <ニックネームテーブル>)s where s.row_number = 1;

PostgreSQLで試しました。

追加情報(rank(), row_number()が使えるSQL)

mysql 8.0.2からウィンドウ関数（rank(), row_number()など）が使えるという情報があります。MariaDBも10.2からサポートされているので確認しました。

確認した実行環境
Fedora 29 x86_64
MariaDB 10.3

設定ファイル(my.ini/my.cnf相当の設定ファイル)
/etc/my.cnf.d/mariadb-server.cnf
以下を追加していたために標準SQLの動作になり、ウィンドウ関数が使えませんでした。
sql-mode="ANSI,ONLY_FULL_GROUP_BY"
コメント化。デフォルトはsql-modeがありません。

ウィンドウ関数の実行SQL
select ID,ニックネームID,優先順位,rank() over (partition by ニックネームID order by ニックネームID, 優先順位)順位,row_number() over (partition by ニックネームID order by ニックネームID, 優先順位)連番 from <ニックネームテーブル>;

|ID|ニックネームID|優先順位|順位|連番|
|:--:|:--:|:--:|
|1 |2 |1 |1|1|
|2 |2 |1 |1|2|
|3 |2 |5 |3|3|
|4 |2 |10 |4|4|
|5 |6 |3 |1|1|
|6 |7 |1 |1|1|

(ニックネームID)でパーティションを作成し、優先順位の昇順に並べる。各パーティション内で、順位(rank())、連番(row_number())を振る。各パーティション内で、順位=1が優先度が最も高い(複数件存在することがある)。連番=1が優先度が最も高く先頭にいるレコード(1件)。

Comments

[shakevo]

ご回答頂きありがとうございます。
確かに、ニックネームIDごとにグルーピングして、優先順位は一番小さいものを集計して表示すれば同じことが出来ますね！
こちらはクエリが冗長になりすぎずすごく良さそうです。

ありがとうございます、こちらも検討させてください。
高評価+１させて頂きます。

[退会済みユーザー]

rank(),row_number()のほうが効率が良いと思います。mysql 8.0.2からウィンドウ関数が使えるという情報があります。回答を追記する予定です。

[shakevo]

ご丁寧に回等頂きありがとうございます、確認させて頂きます。

Answer 3

ニックネームIDは重複しないように、かつ優先順位が一番高い(値が小さいもの)を取得

定義があいまいです

ID=7と8は同じニックネームID、優先順位なので

• ニックネームの重複回避をとるか
• 同じだったときにどちらかのIDが優先されるようなロジックを提示

のどちらかが必要になります

追記

ちなみにnickNameIDと優先順位が同じだったときはnoの小さいものを選ぶとこうです

• 元データ

create table tbl(ID int primary key,nicknameID int,priority int);
insert into tbl values
(1,20,1),
(2,20,2),
(3,20,3),
(4,21,2),
(5,21,3),
(6,23,2),
(7,24,1),
(8,24,1),
(9,24,4),
(10,25,3),
(11,25,3),
(12,25,4),
(13,25,4),
(14,26,2),
(15,26,3),
(16,26,4),
(17,26,4),
(18,26,4);

• 抽出

select * from tbl as t1
where not exists(select 1 from tbl as t2 where t1.nicknameID=t2.nicknameID and (t1.priority>t2.priority or (t1.priority=t2.priority and t1.ID>t2.ID)))

Comments

[shakevo]

サブクエリでちゃんと数値を指定して抽出するのですね。
抽出例を頂きありがとうございます。大変参考になります。

nicknameIDとpriorityでorder by してdistinctすると、
> ID=7と8は同じニックネームID、優先順位なので

といったあいまいな定義がある為、必ずしもソート順が一致せず、
値が正常に取れない可能性があるということでしょうか？

[yambejp]

ユニークにデータをとるときに条件がないというのは条件が競合しているということです
ソートしてlimit 1すれば1件だけ「なにか」がヒットしますが
なにがヒットするかは提示した条件の範囲内で適当（いいかげん）なものになるでしょう
いつも同じデータを取得したいのであれば、厳密に条件を定義する必要があるということです

[shakevo]

ありがとうございます、納得いたしました。
ベストアンサーとさせて頂きます。
ご回答頂きありがとうございました。