Q&A
kadaiテーブルの具体的なサンプルをてcreate table+insert形式で提示してください
前提・実現したいこと
生徒が課題を提出し、教員は課題ごとに提出状況を確認する。というものを作成しております。
今回、生徒が出した課題を「課題番号」「提出数」「未提出数」と表にしたいです。
課題が１から３まであるとして、
| kadai_num | 提出数 | 未提出数 |
|---|---|---|
| 1 | 3 | 1 |
| 2 | 4 | 2 |
のような形にしたいです。
kadaiテーブル
CREATE table kadai{
id int(11) AUTO_INCREMENT
kadai_num int(11)
hyoka int(11) NULL
comm text
kamoku_id int(11)
user int(11)
teisyutsu int(11) DEFAULT 0
}
です。
評価は1が○、0が△、NULLが×。
提出は1が提出、0が未提出となっています。
該当のソースコード
php
1<?php 2$dsn= "localhost"; 3$username = "root"; 4$password = ""; 5$pdo = new PDO($dsn, $username, $password); 6 7$query ='SELECT DISTINCT kadai_num 8 FROM kadai'; 9$stmt = $pdo->query($query); 10$rows = $stmt->fetchAll(); 11 12$sql = 'SELECT count(*) 13 from kadai 14 WHERE teisyutsu = 1'; //出している数 15$stmt2 = $pdo->query($sql); 16$test = $stmt2->fetchColumn(); 17 18$sql = 'SELECT count(*) 19 from kadai 20 WHERE hyoka IS NULL'; //出していない数 21$stmt3 = $pdo->query($sql); 22$test2 = $stmt3->fetchColumn(); 23 24?> 25<!DOCTYPE html> 26<html lang="ja"> 27 <head> 28 <meta charset="utf-8mb4"> 29 <link rel="stylesheet" type="text/css" href="css/default.css"> 30 </head> 31 <body> 32 <table border="2"> 33 <tr>Web</tr> 34 <th>課題番号</th> 35 <th>提出済み</th> 36 <th>未提出</th> 37 <?php 38 39 foreach($rows as $row){ 40 echo "<tr>"; 41 echo "<td>{$row['kadai_num']}</td>"; 42 echo "<td>{$test}</td>"; 43 echo "<td>{$test2}</td>"; 44 echo "</tr>"; 45 } 46 ?> 47 </table> 48 </body> 49 50</html> 51
###表示結果
自身の知識が未熟なため
・課題１の提出数と未提出数が、課題２以降にも表示されている（SQL文,phpから間違っているが、改善方法がわからない）
→kadai_numごとに提出数と未提出数を表示させたい。
・表示結果では提出４、未提出３となっているがテーブルに全部で７つはいっており、そのうちkadai_numが1なのは3つのみ。（kadaiテーブルすべてが出てきている。）
→解決いたしました。
以上の2つがわかりません。
ご意見、ご教授お願いいたします。
すみません、記述しておりませんでした。
phpMyAdminで管理していたため、あっているかはわかりませんが
CREATE table kadai{
id int(11) AUTO_INCREMENT
kadai_num int(11)
hyoka int(11) NULL
comm text
kamoku_id int(11)
user int(11)
teisyutsu int(11) DEFAULT 0
}
です。
評価は1が○、0が△、NULLが×。
提出は1が提出、0が未提出となっています。
質問本文を更新してください。
> 提出は1が提出、0が未提出となっています。
これだとhyoka IS NULLだと「提出はされたけど未評価」か「提出すらされてない」のか判断できないのでteisyutsuだけで「提出済み」「未提出」を判断すれば良いのでは？
ありがとうございます。更新いたしました。
teisyutsuのカラムを追加した後、hyoka IS NULLの部分を更新しておりませんでした。
teisyutsuだけで「提出済み」と「未提出」を判断するようにしました。
NULLは特別な値というか状態なので、そこに意味をもたせてしまうよりかは、ちゃんと2 1 0や1 0 -1などにするべきかと。IS NULLやIS NOT NULLの絞り込みって昔はインデックス効かなかった気がするけど、今はどうなんですかね。「「提出はされたけど未評価」か「提出すらされてない」のか判断できない」にもつながるので、実用的な値の見直しが必要だと思います。
回答2件
0
ベストアンサー
・課題１の提出数と未提出数が、課題２以降にも表示されている（SQL文,phpから間違っているが、改善方法がわからない）
$testも$test2も課題番号で絞られていないので
$testは「teisyutsu = 1で絞り込まれた全件の合計件数」が取得されます。
$test2は「hyoka IS NULLで絞り込まれた全件の合計件数」が取得されます。
SQLはPHPからすると外部の仕組みですので、「どういう情報が取得できるか」は
DBに対して直に実行して確認すると良いです。
ですので、取得できているのは「課題１の提出数と未提出数」ではありません。全部です。
もちろん、$rowsを取得しているSQLも確認すべきですね。
根本解決
テーブル定義が不明ですがGROUP BYとサブクエリ使ってSQL一発で出せそうに思います。
※構文チェックだけ通った未検証コード
SQL
1SELECT 2 a.kadai_num, 3 COUNT(t.kadai_num) AS teisyutsu_count, 4 COUNT(h.kadai_num) AS miteisyutsu_count 5FROM 6 kadai a 7LEFT JOIN( 8 SELECT 9 kadai_num, 10 COUNT(*) 11 FROM 12 kadai 13 WHERE 14 teisyutsu = 1 15 GROUP BY 16 kadai_num 17) t 18ON 19 a.kadai_num = t.kadai_num 20LEFT JOIN( 21 SELECT 22 kadai_num, 23 COUNT(*) 24 FROM 25 kadai 26 WHERE 27 -- hyoka IS NULL 28 teisyutsu = 0 29 GROUP BY 30 kadai_num 31) h 32ON 33 a.kadai_num = h.kadai_num 34GROUP BY 35 a.kadai_num
投稿2019/08/05 01:03
編集2019/08/05 02:21総合スコア80850
hyoka IS NULLの部分を更新し、$test,$test2のSELECT文にGROUP BYを追加したところ、kadai_numが1の提出数2、未提出数1となりkadai_numの合計となりました。ありがとうございます。
課題2以降の提出・未提出の表示はforeach文の$test,$test2の変更となるのでしょうか？
むしろSQLを分ける意味ってありません。
私が回答に提示したSQLを見ると分かりますが、1つのSQLで提出・未提出のGROUP BYまで全てやりきったうえで出しています。
出したい情報は課題番号と密接に関わるものですから、SQLを分けることによってデータ同士の関係性が薄くなるのと、何度もSQLを発行・実行することによる負荷を懸念します。
SQLも結構便利なので、できればアプリケーション側では「表示すればいいだけ」のデータをSQLで作ってしまうのが最も効率的です。
0
提出数についてはteisyutsu = 1で拾えると思いますが
未提出は本当にteisyutsu = nullのデータを置いてあるのでしょうか？
（課題を出した時点で全員未提出状態にしてデータを投入しているかどうか？）
もし、上記のような運用方法なら
SQL
1SELECT kadai_num,sum(teisyutsu = 1) as 提出数,sum(hyoka IS NULL) as 未提出数 2FROM kadai 3GROUP BY kadai_num
対象人数から提出済み数を引くだけでよいかもしれません。
対象人数は普通別途ユーザーテーブルなどで管理していると思います。
追記
考え方からするとkadaiテーブルで提示されている処理は
kadaiを管理するのではなくkadai_userの中間テーブルで、別途kadaiとuserを
管理するテーブルがあるとよりSQL的になります
参考)
課題=3つ、ユーザー=7人いたとして、その連携状態が
課題とユーザーの組み合わせ
(1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(2,4),(2,5),(3,1),(3,5),(3,6)
について提出済みとします
SQL
1create table kadai_user( 2id int(11) primary key auto_increment, 3kadai_id int(11), 4user_id int(11), 5teisyutsu int(11) default 0 6); 7 8insert into kadai_user(kadai_id,user_id) 9values 10(1,1),(1,2),(1,3),(1,4),(1,5),(1,6),(1,7), 11(2,1),(2,2),(2,3),(2,4),(2,5),(2,6),(2,7), 12(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7); 13 14update kadai_user set teisyutsu=1 15where concat(kadai_id,",",user_id) in ( 16"1,1","1,2","1,3","1,4", 17"2,1","2,2","2,4","2,5", 18"3,1","3,5","3,6");
- 集計
SQL
1select kadai_id,sum(teisyutsu=1) as 提出済,sum(teisyutsu=0) as 未提出 2from kadai_user 3group by kadai_id
投稿2019/08/05 01:01
編集2019/08/05 02:41総合スコア114814
あなたの回答
tips
プレビュー
