順位を示すSQLをベースに文字項目の抽出

質問

生徒の成績の得意不得意をあらわすSQLを考えています。
MySQL 5.5 CentOS6.3
どの教科がその生徒にとって一番得意科目であり、その教科はクラスで何位なのかを示すSQLです。
「クラスでの相対順位のもっとも高い教科とその順位」を表示します。

kamedaの一番の得意科目(クラスでの相対順位が最上位)は英語で、クラス2位である
ということが分かるようにする予定です。

一度に名前と順位が入るようにするのが理想なのですが、その方法が分かりませんでしたので、別々にアップデートすることとしました。

アップデートには相対順位がもっとも高い教科を示す【順位SQL】とその教科を表示する【教科SQL】の2つを用意します。

【順位SQL】
「kamedaの一番の得意科目(クラスでの相対順位が最上位)は●●で、クラス2位」を表示する順位SQLは下記のSQLでうまくいきました。

【教科SQL】
次に、●●の部分を埋めるために、教科SQLを組みました。
しかしながら、
#1241 - Operand should contain 1 column(s)
になってしまいます。

どこが適切ではないのか、ご教授頂けませんでしょうか?
よろしくお願いいたします。

また、もし、2つの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(完成)】
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)

【教科SQL(未完成)】
UPDATE point_rank
SET
1st_kyoka_name=
kyoka_name
WHERE
(SELECT kyoka_name,(
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, point_rank AS p1
WHERE t1.usr_id = p1.p_usr_id
ORDER BY rank ASC
LIMIT 0 , 1)

#1241 - Operand should contain 1 column(s)

回答No.1

mpro-gram · ベストアンサー

惜しいところまで行ってるのだけど、1st_kyoka_name= kyoka_name WHERE　では、point_rankに存在しないカラム名を指定していることになるので、不可です。
1st_rank をだすときの select 文に kyoka_name カラムを加えたなら、それを from 句にいれて、もう一つ外側に select query を作って、１カラム１行を返すsubqueryを1st_kyoka_nameに代入とする必要があります。
usr_id を指定するには、２つ外となるupdate対象行のカラムを直接指定できないので、t1用のwhere句や order by句 limit句もさらに外側へ出すことになります。
同順のがあると、どちらの教科名になるかは、実行時の運しだい。

select 文で、point_rank と同じカラム構成のデータが作れたら、全カラム一度に insert 可能なので、以下の文で、いけそうです。

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= top_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
;
mysql 5.1 以降なら GROUP_CONCAT() が使えるので、その生徒において、トップとなる教科が複数あれば、カンマで連結して表示するようにしました。
あと、細かいことを言えば、seiseki2のtable で、kyoka_name にindexを付けた方が速くなるのだけど、mysql では、text型にはindex を貼れません。それほど長い文字列が入るとも思えないので、 varchar(20) などで十分では？

お礼

ありがとうございます。
おかげさまで出来ました。
深く御礼申し上げます。
本当にありがとうございました。

回答No.2

mpro-gram

済みません、最後に確認せずに、手前カラムのalias 使えたはずと書き換えたら、group関数の結果となるaliasは、次のsubquery d2内で参照出来なかった。
正しくは、以下
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
;