MySQL サブクエリとグループ化されたクエリ

`Helenlyn_Class` が存在する場合はデータベースを削除します。
データベース `Helenlyn_Class` を作成します。

/*クラステーブル*/
`classes` が存在する場合はテーブルを削除します。
テーブル「classes」を作成します（
 `classid` int 主キー AUTO_INCREMENT コメント 'クラス ID',
 `classname` varchar(30) DEFAULT NULL コメント 'クラス名'
) ENGINE=InnoDB コメント 'クラス テーブル';

`classes`(`classname`) に挿入します
値（'中学校1年生'）、（'中学校2年生'）、（'中学校3年生'）；

/*学生テーブル: ここでは学生IDと名前の両方が一意であると仮定します*/

`students` が存在する場合はテーブルを削除します。
テーブル「学生」を作成（
 `studentid` int 主キー NOT NULL AUTO_INCREMENT コメント '学生ID',
 `studentname` varchar(20) DEFAULT NULL コメント '学生名',
 `score` DECIMAL(10,2) DEFAULT NULL コメント '卒業スコア',
 `classid` int(4) DEFAULT NULL comment 'クラステーブルのクラスIDからのクラスID'
)ENGINE=InnoDB コメント 'student table';
`students`(`studentname`,`score`,`classid`) の値を挿入します
('ブランド',97.5,1),('ヘレン',96.5,1),('リン',96,1),('ソル',97,1),('ウェン',100,1),('ディニー',92.7,1),
('b1',81,2)、('b2',82,2)、('b3',83,2)、('b4',84,2)、('b5',85,2)、('b6',86,2)、
('c1',71,3)、('c2',72.5,3)、('c3',73,3)、('c4',74,3)、('c5',75,3)、('c6',76,3);


/*卒業評価スコアランキング表*/
`scores` が存在する場合はテーブルを削除します。
テーブル「スコア」を作成します(
 `scoregrad` varchar(3) 主キーコメント 'グレード: S、A、B、C、D'、
 `downset` int コメント 'スコア評価下限',
 `upset` int コメント 'スコア評価上限'
）コメント「卒業評価スコアランキング表」
`scores` に値（'S'、91、100）、（'A'、81、90）、（'B'、71、80）、（'C'、61、70）、（'D'、51、60）を挿入します。

mysql> クラス番号としてa.classid、クラス名としてa.classnameを選択し、
クラス a の生徒数として (select count(*) from students b where b.classid = a.classid) を指定します。
+----------+----------+----------+
| クラス番号| クラス名| 生徒数|
+----------+----------+----------+
| 1 | 1年生、9年生 | 6 |
| 2 | 2年生、9年生 | 6 |
| 3 | 3年生、9年生 | 6 |
+----------+----------+----------+
3行セット

mysql>選択
(a.classid = b.classid かつ b.studentname='brand' のクラス a、生徒 b からクラス名を選択)
クラスとして;
+----------+
| クラス|
+----------+
| クラス 1、グレード 9|
+----------+
セット内の1行

mysql> students から a.classid,avg(a.score) を選択し、 a.classid でグループ化します。

+---------+--------------+
| クラスID | 平均(a.スコア) |
+---------+--------------+
| 1 | 96.616667 |
| 2 | 83.500000 |
| 3 | 73.583333 |
+---------+--------------+
3行セット

mysql> select * from scores order by upset desc;

+-----------+---------+--------+
| スコアグレード | 落ち込む | 動揺 |
+-----------+---------+--------+
| S | 91 | 100 |
| あ | 81 | 90 |
| B | 71 | 80 |
| C | 61 | 70 |
| D | 51 | 60 |
+-----------+---------+--------+
5行セット

クラスIDとしてa.classid、卒業平均スコアとしてa.avgscore、スコア評価としてb.scoregradを選択します。
(classid、avg(score) を avgscore として、classid で学生グループから選択) として、
スコア b で、a.avgscore は b.downset と b.upset の間です。

+--------+--------------+----------+
| クラス ID | 平均卒業スコア | スコア評価 |
+--------+--------------+----------+
| 1 | 96.616667 | S |
| 2 | 83.500000 | A |
| 3 | 73.583333 | B |
+--------+--------------+----------+
3行セット

mysql> select * from students a where a.score >(select b.score from students b where b.studentname='diny');
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 5 | ウェン | 100 | 1 |
+-----------+-------------+--------+---------+
5行セット

mysql> 学生から*を選択
a.score <(b.studentname='diny' の students b から b.score を選択)
かつ a.classid <> (select b.classid from students b where b.studentname='diny') ;
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+-------------+--------+---------+
12行セット

mysql> a.classid、avg(a.score) を a.classid でグループ化した students から avgscore として選択します
平均スコア < (生徒から平均スコアを選択) を持つこと。
+---------+------------+
| クラスID | 平均スコア |
+---------+------------+
| 2 | 83.500000 |
| 3 | 73.583333 |
+---------+------------+
2行セット

mysql> select * from students a where a.classid in (select distinctive b.classid from classes b where b.classid <3);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 5 | ウェン | 100 | 1 |
| 6 | ディニー | 92.7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+-------------+--------+---------+
12行セット

mysql> select * from students a where a.classid = any (select distinctive b.classid from classes b where b.classid <3);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 5 | ウェン | 100 | 1 |
| 6 | ディニー | 92.7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+-------------+--------+---------+
12行セット

mysql> select * from students a where a.classid <> all (select distinctive b.classid from classes b where b.classid <3);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+-------------+--------+---------+
6行セット

mysql> select * from students a where (a.studentid, a.score) in (select max(studentid),min(score) from students);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 19 | ララ | 51 | 0 |
+-----------+-------------+--------+---------+
セット内の1行

mysql> クラス名を classes a から選択します。存在する場合は、(学生 b から 1 を選択します。b.classid = a.classid);

+-----------+
|クラス名|
+-----------+
| クラス 1、グレード 9|
| クラス 2、グレード 9|
| クラス 3、グレード 9|
+-----------+
3行セット

mysql> クラス名を classes a から選択します。ここで、a.classid は students から classid を選択します。

+-----------+
|クラス名|
+-----------+
| クラス 1、グレード 9|
| クラス 2、グレード 9|
| クラス 3、9 年生|
+-----------+
3行セット

 条件1でtnameからcname1、cname2を選択
 連合
 条件2でtnameからcname1、cname2を選択

 tname1 から t1_cname1,t1_cname2 を選択する (条件付き)
 連合
 条件付きでtname2からt2_cname1、t_2cname2を選択