min句のSQLを改造し二番目に小さいdata抽出

質問

2度目の質問となり恐縮しております。
生徒の成績の得意不得意をあらわすSQLを考えています。(mySQL 5.5 centOS6.3)
テストの成績をもとに
「kamedaの一番の得意科目(クラスでの相対順位が最上位)は英語で、クラス2位である」
「kamedaの二番の得意科目は数学で、クラス4位である」
「kamedaの三番の得意科目は国語で、クラス7位である」
ということが分かるようにする予定です。

当初私が考えた案ではlimit句を使った方法を考えており、limit後の値を変更することにより2位、3位を出す予定でしたが、私の方法ではうまくいかなかったためこのサイトで質問をさせて頂いたところ、minを使った方法を紹介れました。
(私の質問方法が「クラスの最上位を求める」というように省略して記載していたためmin句での回答を頂くことになりました)

この回答をベースに当方で2番目を抽出出来るよう改造に着手しましたがうまく出来ません。
●min句ベースではminから2番目というものをどうしても取ることが出来ませんでした。
●このmin句ベースのものをlimit句のSQLに改造しようとトライしたのですがどうしても改造することが出来ませんでした。

何度もこの場をお借りして恐縮ですがお知恵を拝借したくお願い申し上げます。

****************************************************
ベースとなるデータ

CREATE table seiseki2
(usr_id TEXT,kyoka_name TEXT,point INT);

CREATE table point_rank
(p_usr_id TEXT,1st_kyoka_name TEXT,1st_rank INT);

INSERT INTO seiseki2
(usr_id,kyoka_name,point)
values
('kameda','数学',85),('suzuki','数学',71),('kaneko','数学',32),('yosida','数学',61),('tanita','数学',70),('suyama','数学',80),('kisida','数学',61),('komine','数学',99),('tomita','数学',89),('sugita','数学',75),('kameda','国語',80),('suzuki','国語',46),('kaneko','国語',52),('yosida','国語',89),('tanita','国語',77),('suyama','国語',67),('kisida','国語',81),('komine','国語',89),('tomita','国語',69),('sugita','国語',70),('kameda','英語',94),('suzuki','英語',86),('kaneko','英語',50),('yosida','英語',59),('tanita','英語',48),('suyama','英語',97),('kisida','英語',74),('komine','英語',82),('tomita','英語',59),('sugita','英語',60);

INSERT INTO point_rank
(p_usr_id,1st_kyoka_name,1st_rank)
values
('kameda','',''),('suzuki','',''),('kaneko','',''),('yosida','',''),('tanita','',''),('suyama','',''),('kisida','',''),('komine','',''),('tomita','',''),('sugita','','');

*****************************************************

この場で回答頂きましたSQL
下記はmin句を使用して最上位の教科を出すことが出来ます。
今回の質問は下記を改良し、2番目の教科、3番目の教科を出したいと考えています

INSERT INTO point_rank (p_usr_id, 1st_rank, 1st_kyoka_name )
SELECT d1.id
, min(d1.rank) AS top_rank
, (select GROUP_CONCAT( kyoka )
from (SELECT t1.usr_id as id , t1.kyoka_name as kyoka
,( SELECT count( * ) +1
FROM seiseki2 AS t2
WHERE t2.point > t1.point AND t2.kyoka_name = t1.kyoka_name
) AS rank
FROM seiseki2 AS t1
) as d2
where d2.id=d1.id and d2.rank= min(d1.rank)
group by d2.id
) AS kyoka
FROM ( select t1.usr_id as id
,( SELECT count( * ) +1
FROM seiseki2 AS t2
WHERE t2.point > t1.point AND t2.kyoka_name = t1.kyoka_name
) AS rank
FROM seiseki2 AS t1
) as d1
group by d1.id
;

*********************************************

ご参考
私が当初考えたSQL　順位は出ますが教科が出せません

UPDATE point_rank
SET
1st_rank=
(SELECT (
SELECT count( * ) +1
FROM seiseki2 AS t2
WHERE 1
AND t2.point > t1.point
AND t2.kyoka_name = t1.kyoka_name
) AS rank
FROM seiseki2 AS t1
WHERE t1.usr_id = point_rank.p_usr_id
ORDER BY rank ASC
LIMIT 0 , 1)

回答No.2

yamada_g · ベストアンサー

>「kamedaの一番の得意科目(クラスでの相対順位が最上位)は英語で、クラス2位である」
>「kamedaの二番の得意科目は数学で、クラス4位である」
>「kamedaの三番の得意科目は国語で、クラス7位である」
とテーブルの更新内容が一致しなかったので、上記の結果を出すためのSELECT文です。
select
　t1.usr_id, t1.kyoka_name, count(t2.usr_id) + 1 my_rank,t1.kyoka_rank
from
　(　select
　　　t1.usr_id, t1.kyoka_name,count(t2.usr_id) + 1 kyoka_rank
　　from seiseki2 t1
　　left join seiseki2 t2 on (t1.usr_id <> t2.usr_id and t1.kyoka_name = t2.kyoka_name and t1.point < t2.point)
　　group by t1.usr_id, t1.kyoka_name
　) t1
　left join
　(　select
　　　t1.usr_id, t1.kyoka_name,count(t2.usr_id) + 1 kyoka_rank
　　from seiseki2 t1
　　left join seseki2 t2 on (t1.usr_id <> t2.usr_id and t1.kyoka_name = t2.kyoka_name and t1.point < t2.point)
　　group by t1.usr_id, t1.kyoka_name
　) t2 on (t1.usr_id = t2.usr_id and t1.kyoka_name <> t2.kyoka_name and t1.kyoka_rank > t2.kyoka_rank)
group by t1.usr_id, t1.kyoka_name,t1.kyoka_rank
order by usr_id,my_rank
;

全角スペースでインデントしてあります。
mysqlの文法に合っていない箇所は適宜修正してください。

お礼

ありがとうございます。
この方法で順位の確認が出来ました。
ありがとうございました!!
お忙しい中、お時間を頂戴いたしましたこと深く御礼申し上げます。

当方の場合、大元のテーブルの関係上アップデート構文とする必要がありましたので、下記の形で1位を入力（アップデート)、limit構文を変更することにより2位、3位　と順にインサートすることといたしました。

UPDATE point_rank
SET
1st_kyoka_name=
(SELECT t3.kyoka_name
FROM (
SELECT t1.usr_id, t1.kyoka_name, count( t2.usr_id ) +1 my_rank, t1.kyoka_rank
FROM (
SELECT t1.usr_id, t1.kyoka_name, count( t2.usr_id ) +1 kyoka_rank
FROM seiseki2 t1
LEFT JOIN seiseki2 t2 ON ( t1.usr_id <> t2.usr_id
AND t1.kyoka_name = t2.kyoka_name
AND t1.point < t2.point )
GROUP BY t1.usr_id, t1.kyoka_name
)t1
LEFT JOIN (
SELECT t1.usr_id, t1.kyoka_name, count( t2.usr_id ) +1 kyoka_rank
FROM seiseki2 t1
LEFT JOIN seiseki2 t2 ON ( t1.usr_id <> t2.usr_id
AND t1.kyoka_name = t2.kyoka_name
AND t1.point < t2.point )
GROUP BY t1.usr_id, t1.kyoka_name
)t2 ON ( t1.usr_id = t2.usr_id
AND t1.kyoka_name <> t2.kyoka_name
AND t1.kyoka_rank > t2.kyoka_rank )
GROUP BY t1.usr_id, t1.kyoka_name, t1.kyoka_rank
ORDER BY usr_id, my_rank
) AS t3
WHERE t3.usr_id = point_rank.p_usr_id
ORDER BY t3.kyoka_rank ASC
LIMIT 0 , 1),
1st_rank=
(SELECT (
SELECT count( * ) +1
FROM seiseki2 AS t2
WHERE 1
AND t2.point > t1.point
AND t2.kyoka_name = t1.kyoka_name
) AS rank
FROM seiseki2 AS t1
WHERE t1.usr_id = point_rank.p_usr_id
ORDER BY rank ASC
LIMIT 0 , 1);

回答No.1

yambejp

たとえば別教科でランクが同じものをどちら得意と判断するのかなど
仕様のつめがあまそうですが、以下でどうでしょう。

create table usr_kyoka_rank(usr_id varchar(20) not null primary key,`数学` int,`国語` int,`英語` int);
insert ignore into usr_kyoka_rank
select usr_id
,sum((kyoka_name='数学')*rank) as `数学`
,sum((kyoka_name='国語')*rank) as `国語`
,sum((kyoka_name='英語')*rank) as `英語`
FROM
(SELECT
(SELECT COUNT(*) +1
FROM seiseki2 AS t2
WHERE 1
and t1.point < t2.point
and t1.kyoka_name=t2.kyoka_name
) AS RANK
,`t1`.*
FROM seiseki2 AS t1) as t3
group by usr_id

集計されたusr_kyoka_rankテーブルを使って好きに判断してください

お礼

ありがとうございます。
この方法で順位のインサートが出来ました。
ありがとうございました!!
お忙しい中、お時間を頂戴いたしましたこと深く御礼申し上げます。