[MySQL、PHP]異なるカラムを比較して上位x件を取得したい

Question

下記のようなDBテーブルの構成があります。

|id|name|kokugo|sugaku|eigo|
:--|:--:|--:|
|1|佐藤|70|80|85|
|2|鈴木|60|90|80|
|3|高橋|80|55|95|
|4|川島|85|95|70|
|5|若林|60|75|90|

この場合、個人個人の点数で、良い点数の科目上位２つを取りたいです。
例えば佐藤さんなら、eigoとsugakuの順で２つだけselect、鈴木さんならsugaku、eigoの順で2つだけselectみたいな感じです。

SQLだとどうやって良いのかまったくわからずでして。
ちなみに取得データを配列に入れるのですが、その処理はPHPで行うので、一旦取得してきてPHPで並び替えるでも良いのですが、どっちにしろアルゴリズムが思いつかずでして。。

取得後のデータのイメージとしては、下記のような感じです。
$hoge['佐藤'] = ['eigo' => 85, 'sugaku' => 80];
$hoge['鈴木'] = ['sugaku' => 90, 'eigo' => 80];

ちなみに同じ点数だった場合は、今のカラムの並び順を優先としたいです。
（kokugo,sugaku,eigo）

3つとも同じ場合は、kokugo,sugakuの順で2つ取得したいです。

ご教示いただけると幸いです。

Comments

[yambejp]

TOP2教科が同じ点数だった場合表示上どちらが先になるのでしょうか？
３教科すべてが同じ点数だった場合どうするのでしょうか？

[Discord]

コメントありがとうございます！
同じ点数だった場合は、今のカラムの並び順を優先にしたいです。
（kokugo,sugaku,eigo）

3つとも同じ場合は、kokugo,sugakuの順で2つ取得したいです。
質問も修正しておきます。

[yambejp]

追加で申し訳ないですがmysqlのバージョンを明記ください

[m.ts10806]

PHPはオブジェクト操作になるし二度手間になります。
SQLで使いたい形式で取るほうが簡単です。

[Discord]

mysqlのバージョンが5.7です！

Answer 1

まずカラム同士の比較をするならデータの持ち方がおかしいです。
むりやり処理をするなら一旦ビューをつくるとよいでしょう

/* 元データ */
create table tbl(ID int primary key,name varchar(10),kokugo int,sugaku int,eigo int);
insert into tbl values
(1,'佐藤',70,80,85),
(2,'鈴木',60,90,80),
(3,'高橋',80,55,95),
(4,'川島',85,95,70),
(5,'若林',60,75,90),
(6,'坂本',90,90,90);

/*ビューの作成*/
create view v as
select ID,kyoka,point,(select count(*)+1 from(
select *,case kyoka when 'kokugo' then 1 when 'sugaku' then 2 else 3 end as sort
from(
select ID,'kokugo' as kyoka ,kokugo as point from tbl
union all select ID,'sugaku',sugaku from tbl
union all select ID,'eigo',eigo from tbl
) as t3 )as t4 where t2.ID=t4.ID and (t2.point<t4.point or t2.point=t4.point and t2.sort>t4.sort)) as rank
 from(
select *,case kyoka when 'kokugo' then 1 when 'sugaku' then 2 else 3 end as sort
from(
select ID,'kokugo' as kyoka ,kokugo as point from tbl
union all select ID,'sugaku',sugaku from tbl
union all select ID,'eigo',eigo from tbl
) as t1 )as t2;

select * from v;

ここから上位２個をとりだしますが、順位順にとりだすならjsonデータなどにしないといけません

select ID,concat('[',group_concat(concat('{"',kyoka,'"',':',point,',"rank":',rank,'}') order by rank separator ',' ),']')
as json
from v
where rank<=2
group by ID

結果：

ID	JSON
1	[{"eigo":85,"rank":1},{"sugaku":80,"rank":2}]
2	[{"sugaku":90,"rank":1},{"eigo":80,"rank":2}]
3	[{"eigo":95,"rank":1},{"kokugo":80,"rank":2}]
4	[{"sugaku":95,"rank":1},{"kokugo":85,"rank":2}]
5	[{"eigo":90,"rank":1},{"sugaku":75,"rank":2}]
6	[{"kokugo":90,"rank":1},{"sugaku":90,"rank":2}]

Comments

[Discord]

ありがとうございます！
たしかにビューを作成すればいけそうですね。

ちなみにこういった場合、どういうデータの持たせ方が良いのでしょうか。

[yambejp]

＞ どういうデータの持たせ方が良い

ビューで表示されるような持ち方です
（ただしrankは都度計算することになりますが）

[Discord]

なるほど、ありがとうございます！
そのあたりも含めて再考してみたいと思います。
非常に助かりました。