■ はじめに
例えば... 全体数とその母数からさらに条件を付けてカウントしたい場合に それをサブクエリを使わずに、一回で行う方法を考える
目次
【1】解決案 【2】サンプル
【1】解決案
COUNT (条件 OR null) でカウント可能。
【2】サンプル
* 全体数(count_all_student)とその内80点以上のデータ数を数える * DB : MySQL5.7
テーブル
-- 学生 CREATE TABLE IF NOT EXISTS `student` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ; -- 科目 CREATE TABLE IF NOT EXISTS `subject` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(10) NOT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ; -- 点数 CREATE TABLE IF NOT EXISTS `score` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `student_id` INT(11) NOT NULL, `subject_id` INT(11) NOT NULL, `score` INT(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), INDEX `FK_score_student` (`student_id`), INDEX `FK_score_subject` (`subject_id`), INDEX `date` (`date`), CONSTRAINT `FK_score_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`), CONSTRAINT `FK_score_subject` FOREIGN KEY (`subject_id`) REFERENCES `subject` (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB AUTO_INCREMENT=1 ;
データ
-- 学生 REPLACE INTO `student` (`id`, `name`) VALUES (1, 'Mike'), (2, 'Tom'), (3, 'Sam'); -- 科目 REPLACE INTO `subject` (`id`, `name`) VALUES (1, 'Japanese'), (2, 'Math'), (3, 'Science'), (4, 'Social'), (5, 'English'); -- 点数 REPLACE INTO `score` (`id`, `date`, `student_id`, `subject_id`, `score`) VALUES (1, '2018-04-12', 1, 1, 40), (2, '2018-04-12', 2, 1, 43), (3, '2018-04-12', 3, 1, 86), (4, '2018-04-12', 1, 2, 21), (5, '2018-04-12', 2, 2, 43), (6, '2018-04-12', 3, 2, 32), (7, '2018-04-12', 1, 3, 54), (8, '2018-04-12', 2, 3, 87), (9, '2018-04-12', 3, 3, 76), (10, '2018-04-13', 1, 4, 65), (11, '2018-04-13', 2, 4, 98), (12, '2018-04-13', 3, 4, 87), (13, '2018-04-13', 1, 5, 76), (14, '2018-04-13', 2, 5, 45), (15, '2018-04-13', 3, 5, 84);
データ確認
SELECT sb.name AS subject_name, st.name AS student_name, sc.score AS score FROM score AS sc INNER JOIN student AS st ON st.id = sc.student_id INNER JOIN subject AS sb ON sb.id = sc.subject_id ORDER BY sb.id, st.id
出力結果
subject_name | student_name | score -------------+--------------+-------- Japanese | Mike | 40 Japanese | Tom | 43 Japanese | Sam | 86 ------------------------------------- Math | Mike | 21 Math | Tom | 43 Math | Sam | 32 ------------------------------------- Science | Mike | 54 Science | Tom | 87 Science | Sam | 76 ------------------------------------- Social | Mike | 65 Social | Tom | 98 Social | Sam | 87 ------------------------------------- English | Mike | 76 English | Tom | 45 English | Sam | 84
SQL文
SELECT sb.name AS subject_name, COUNT(*) AS count_all_student, COUNT(sc.score >= 80 OR null) AS count_high_score FROM score AS sc INNER JOIN student AS st ON st.id = sc.student_id INNER JOIN subject AS sb ON sb.id = sc.subject_id GROUP BY sc.subject_id
出力結果
subject_name | count_all_student | count_high_score -------------+-------------------+------------------ Japanese | 3 | 1 Math | 3 | 0 Science | 3 | 1 Social | 3 | 2 English | 3 | 1
参考文献
https://qiita.com/kazu56/items/8d1118557a5eb670ce6d
http://d.hatena.ne.jp/tacohachi/20100206/p1
関連記事
過去直近データを取得するには
https://dk521123.hatenablog.com/entry/2016/01/05/234938
最新日付データを取得するには
https://dk521123.hatenablog.com/entry/2014/10/09/004105
期間が重なるデータを取得するには
https://dk521123.hatenablog.com/entry/2011/12/17/025502
順位付け・ランキング ~ RANK / DENSE_RANK ~
https://dk521123.hatenablog.com/entry/2012/08/15/225233
初めの数文字が一致してたら同一データと見なすSQLを考える
https://dk521123.hatenablog.com/entry/2018/04/11/225229