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ベストアンサー
- 回答日時:
>「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
- 回答日時:
たとえば別教科でランクが同じものをどちら得意と判断するのかなど
仕様のつめがあまそうですが、以下でどうでしょう。
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テーブルを使って好きに判断してください
ありがとうございます。
この方法で順位のインサートが出来ました。
ありがとうございました!!
お忙しい中、お時間を頂戴いたしましたこと深く御礼申し上げます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- MySQL SQLです。こんな感じですか?あってますか? うまくいきません教えてくださいお願いします 1 2023/07/08 15:27
- MySQL うまくいきません教えてくださいお願いしますSQLです。クエリ構文です。 1 2023/07/07 12:39
- PHP コメント機能に返信欄を矢印で追加したい 1 2022/05/09 21:17
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 第二回模試の3科目の各得点と合 1 2023/04/25 18:02
- Access(アクセス) アクセス 有効なフィールド名、または式として認識できませんのエラー 3 2022/08/19 11:53
- C言語・C++・C# C言語 プログラミング 4 2022/05/22 11:53
- MySQL 三科目合計点のクラス別平均点求めるクエリ式を教えてください 1 2023/07/04 09:44
- Access(アクセス) アクセス where句を使用して複数条件抽出をするには 2 2022/08/29 13:24
- Oracle sqlで質問です。 aテーブルとbテーブルがあり、下記のsqlで取得したidとnameに一致しないレ 1 2022/04/20 20:34
- MySQL SQLです。下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 「昨年の各月の総降 1 2023/07/01 00:32
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
副問合せの書き方について
-
期間の重複を調べるSQL文につい...
-
MySQLのDATE型カラム値がNULLの...
-
親と子供が複数のSQL取得方法
-
SQLサーバから、項目の属性(型...
-
複数DBテーブルからのデータ取得
-
SQLにて特定の文字を除いた検索...
-
1テーブル&複数レコードの更新...
-
SQL Left Join で重複を排除す...
-
上位3位を求めるSQL文は?
-
insertを高速化させたい
-
ある時間以内の利用者の抽出に...
-
別テーブルからSELECTした値を...
-
Access パラメータクエリをcsv...
-
WordpressのContact form 7でzi...
-
「SELECTして取得できない場合...
-
阪急三番街 ATM(ゆうちょ)は...
-
select文のwhere句に配列を入れ...
-
MySQLの検索について(カンマ区...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
副問合せの書き方について
-
SQLサーバから、項目の属性(型...
-
SQL Left Join で重複を排除す...
-
select文のwhere句に配列を入れ...
-
VIEWの元のテーブルのindexって...
-
PL/SQLの変数について
-
マイクラPC版のコマンドで効率...
-
エクセルの関数について教えて...
-
SQLにて特定の文字を除いた検索...
-
sqlで、600行あるテーブルを100...
-
Access パラメータクエリをcsv...
-
Unionした最後にGROUP BYを追加...
-
inner joinをすると数がおかし...
-
複数テーブルのGROUP BY の使い...
-
ある条件の最大値+1を初番する...
-
MySQLのint型で001と表示する方...
-
クエリ表示と、ADOで抽出したレ...
-
[MySQL] UNIQUE制約の値を更新...
-
テーブル名を省略して「h.id」...
おすすめ情報