DBからデータを取得することはかなり多いと思います。
なので、色んなSELECT文の方法を記載したいと思います。
データを全て取得する
1 2 3 4 5 |
SELECT * FROM questions ; |
取得したいデータを取得する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT id, m_categories_id, body1, select1, select2, select3, select4, answer, status, shared_with, created_at FROM questions ; |
取得するデータの条件を設定
[カラムaが1のデータを取得]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT id, m_categories_id, body1, select1, select2, select3, select4, answer, status, shared_with, created_at FROM questions WHERE id = 1 ; |
[カラムaが1〜10のデータを取得]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT id, m_categories_id, body1, select1, select2, select3, select4, answer, status, shared_with, created_at FROM questions WHERE id >= 1 AND id <= 10 ; |
CASE文
[カラムdのデータよって表示させるデータを変えている]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT id, body1, status, shared_with, CASE q.answer WHEN 1 THEN q.select1 WHEN 2 THEN q.select2 WHEN 3 THEN q.select3 WHEN 4 THEN q.select4 ELSE '' END answer FROM questions as q ; |
answerが1の時はselect1のデータを表示
answerが2の時はselect2のデータを表示
answerが3の時はselect3のデータを表示
answerが4の時はselect4のデータを表示
answerが1〜4以外なら非表示
questions AS q ⇒ エイリアス(別名)を付けています
q.answer ⇒ テーブル名(エイリアス).カラム名
※テーブルを結合した時に、テーブルに同じカラム名があったとします。どちらのテーブルのカラムかわからないのでエラーが出ます。
なので、テーブル名.カラム名とすることで、どのテーブルのカラムかがわかりエラーもなくなります。
[複数あるパターン]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT id, body1, status, CASE q.answer WHEN 1 THEN q.select1 WHEN 2 THEN q.select2 WHEN 3 THEN q.select3 WHEN 4 THEN q.select4 ELSE '' END answer, CASE q.status WHEN 0 THEN 1 WHEN 1 THEN 2 WHEN 2 THEN 3 ELSE '' END status FROM questions as q ; |
[関数を使用するパターン]
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT id, CASE WHEN TIMEDIFF(created, modified) is NULL THEN '00:00:00' ELSE TIMEDIFF(created, modified) END time_diff FROM t_ps_spot WHERE id >= 1 and id <= 10 ; |
今回は modified と createdの時刻の差を取得します。
case文で、取得したデータがNULLだったら00:00:00、NULLじゃなかったら返り値を表示しています。
[t_ps_spot元データ]
1 2 3 4 5 6 7 8 9 10 11 |
id created modified 1 2016-12-11 11:43:09 2016-12-09 10:43:09 2 0000-00-00 00:00:00 2016-12-09 10:43:09 3 2016-12-09 09:43:09 0000-00-00 00:00:00 4 2016-12-09 05:55:09 2016-12-09 05:43:09 5 NULL 2016-12-09 07:43:09 6 2016-12-09 10:00:00 NULL 7 2016-12-10 10:43:09 2016-12-09 09:43:09 8 2016-12-09 10:43:09 2016-12-09 10:43:09 9 2016-12-09 10:43:09 2016-12-09 10:43:09 10 2016-12-09 10:43:09 2016-12-09 10:43:09 |
[実行結果]
1 2 3 4 5 6 7 8 9 10 11 |
id time_diff 1 49:00:00 2 00:00:00 3 00:00:00 4 00:12:00 5 00:00:00 6 00:00:00 7 25:00:00 8 00:00:00 9 00:00:00 10 00:00:00 |
TIMEDIFF
時刻の差分を取得する
1 2 3 4 5 6 7 8 9 |
SELECT id, TIMEDIFF(created, modified) AS diif FROM t_ps_spot WHERE id >= 1 AND id <= 10 ; |
カラムcreated,modifiedのどちらかのデータまたは両方のデータが0000-00-00 00:00:00、NULLであった場合、NULLが返ってくる。
また、不正な引数を渡した場合はNULLが返ってくるようです。
[実行結果]
1 2 3 4 5 6 7 8 9 10 11 |
id diff 1 49:00:00 2 NULL 3 NULL 4 00:12:00 5 NULL 6 NULL 7 25:00:00 8 00:00:00 9 00:00:00 10 00:00:00 |
INNER JOIN
内部結合は、それぞれのテーブルの指定したカラムの値が一致するものだけを結合します。
[questionsとjoin]
1 2 3 4 5 6 7 8 9 10 11 |
SELECT q.id, q.m_categories_id, mc.id, mc.name FROM questions as q INNER JOIN m_categories as mc ON q.m_categories_id = mc.id ; |
[question_tagとjoinしてグループで絞ったときに文字列連結]
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT t.id, qt.questions_id, qt.tags_id, GROUP_CONCAT(t.name) as tags_name FROM tags as t INNER JOIN question_tag as qt ON t.id = qt.tags_id group by qt.questions_id ; |
FROM句でのサブクエリの利用
FROM句でのサブクエリの利用は、これまでのサブクエリと性格が異なります。WHERE句、SELECT句でのサブクエリはあくまで結果として値を利用していました。これがFROM句での利用では、サブクエリの結果を1つのテーブルとして扱うことが可能となります。FROM句でのサブクエリの利用は非常に応用性が高く、サブクエリの主だった利用目的はこのFROM句での利用にあるといえるほどです。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT a.questions_id, count(*) AS challenger_count FROM ( SELECT questions_id, users_id FROM attendee_results GROUP BY questions_id, users_id ) AS a group by a.questions_id ; |
ROUND関数&AVG関数
1 2 3 4 5 6 7 8 9 |
SELECT questions_id, count(*) as questions_id_count, ROUND(AVG(is_correct) * 100, 0) AS accuracy_rate FROM attendee_results GROUP BY questions_id ; |
ROUND
四捨五入をしたい時に利用
ROUND ( numeric_expression , length [ ,function ] )
* numeric_expression: 数値
* length: 数値を丸める際の有効桁数 (-2 であれば 100 の位、-1 であれば 10 の位、 0 が 1 の位、1 が小数点第一位、2 が小数点第二位、、、のように続きます)
[ 例 ]
SELECT ROUND(1234.1234, -3); –> 1000.0000
SELECT ROUND(1234.1234, -2); –> 1200.0000
SELECT ROUND(1234.1234, -1); –> 1230.0000
SELECT ROUND(1234.1234, 0); –> 1234.0000
SELECT ROUND(1234.1234, 1); –> 1234.1000
SELECT ROUND(1234.1234, 2); –> 1234.1200
SELECT ROUND(1234.1234, 3); –> 1234.1230
AVG
SQLで平均を出すにはAVG関数を使用します。
構文
SELECT AVG(列名) FROM テーブル名;
個数(行数)をカウントする ~ COUNT ~
COUNTは、個数(行数)をカウントする集計関数です。COUNT関数の場合「レコードの行数」をカウントする関数ですので、どの列を指定してもいいので、こういう場合「*」を指定します。
[question_likes]
1 2 3 4 5 6 7 8 |
SELECT questions_id, count(*) AS how_nice_count FROM question_likes GROUP BY questions_id ; |
[question_reports]
1 2 3 4 5 6 7 8 |
SELECT questions_id, count(*) as notification_count FROM question_reports GROUP BY questions_id ; |
[条件式を加えたパターン]
1 2 3 4 5 6 7 8 9 |
SELECT count(*) AS post_id_count FROM comments WHERE deleted_at IS NULL GROUP BY post_id ; |
[実行結果]
1 2 3 4 5 6 7 8 9 10 11 |
post_id_count 9 9 5 4 9 12 11 8 4 5 |
複数のINNER JOIN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 |
SELECT q.id, q.body1, CASE q.answer WHEN 1 THEN q.select1 WHEN 2 THEN q.select2 WHEN 3 THEN q.select3 WHEN 4 THEN q.select4 ELSE '' END answer, q.status, q.shared_with, category.name, tags.tags_name, tyousensya.challenger_count, seikairitu.accuracy_rate, iine.how_nice_count, tuchi.notification_count, q.created_at FROM questions AS q INNER JOIN ( SELECT questions_id, count(*) AS notification_count FROM question_reports GROUP BY questions_id ) AS tuchi ON q.id = tuchi.questions_id INNER JOIN ( SELECT q.id AS questions_id, mc.name FROM questions AS q INNER JOIN m_categories AS mc On q.m_categories_id = mc.id ) AS category ON category.questions_id = q.id INNER JOIN ( SELECT qt.questions_id, qt.tags_id, GROUP_CONCAT(t.name) AS tags_name FROM tags AS t INNER JOIN question_tag AS qt On t.id = qt.tags_id GROUP BY qt.questions_id ) AS tags ON tags.questions_id = q.id INNER JOIN ( SELECT questions_id, count(*) AS how_nice_count FROM question_likes GROUP BY questions_id ) AS iine ON q.id = iine.questions_id INNER JOIN ( SELECT a.questions_id, count(*) AS challenger_count FROM ( SELECT questions_id, users_id FROM attendee_results GROUP BY questions_id, users_id ) AS a GROUP BY a.questions_id ) AS tyousensya ON tyousensya.questions_id = q.id INNER JOIN ( SELECT questions_id, count(*) AS questions_id_count, round(AVG(is_correct) * 100, 0) AS accuracy_rate FROM attendee_results GROUP BY questions_id ) AS seikairitu ON seikairitu.questions_id = q.id ; |
自分自身にLEFT JOIN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
SELECT q.regist_time, st.skil_id, st.status FROM (( SELECT skil_id, use_years, DATE_FORMAT(use_years, '%Y%m') as regist_time FROM questions WHERE del_flag = 0 GROUP BY DATE_FORMAT(use_years, '%Y%m') ) q) LEFT JOIN ( SELECT skil_id, GROUP_CONCAT(DISTINCT(status) ORDER BY status) as status, use_years, DATE_FORMAT(use_years, '%Y%m') as regist_time FROM questions WHERE use_years >= '2017-04-01' AND use_years <= '2017-05-01' AND del_flag = 0 GROUP BY use_years, skil_id ) st ON st.regist_time = q.regist_time WHERE q.regist_time >= '2017-04-01' AND q.regist_time <= '2017-05-01' ; |
関数について
1 |
GROUP_CONCAT(distinct(status) ORDER BY status) |
GROUP_CONCAT:statusをグループで絞った時に、statusの中身の文字をカンマ区切りで表示します。
DISTINCT:statusの中身の重複した文字を1つにまとめる。
ORDER BY:statusの中身をソートします。