単純にデータベース設計に関わってきたのでメモ程度に書きます。随時更新していく予定です。
==== 08/27更新 ====
ORDER BY
指定したカラムを基準にして昇順・降順でソートします
- ASC・・・昇順(小さい順)
- DESC・・降順(大きい順)
サンプルデータ
ut_user_id |
ut_user_name |
1 |
A |
2 |
B |
3 |
C |
4 |
A |
数値で並び替えするパターン
SELECT
*
FROM
user_tbl
ORDER BY
ut_user_id ASC;
ut_user_id |
ut_user_name |
1 |
A |
2 |
B |
3 |
C |
4 |
A |
SELECT
*
FROM
user_tbl
ORDER BY
ut_user_id DESC;
ut_user_id |
ut_user_name |
4 |
A |
3 |
C |
2 |
B |
1 |
A |
文字列で並び替えするパターン
SELECT
*
FROM
user_tbl
ORDER BY ut_user_name ASC;
ut_user_id |
ut_user_name |
1 |
A |
4 |
A |
2 |
B |
3 |
C |
SELECT
*
FROM
user_tbl
ORDER BY ut_user_name DESC;
ut_user_id |
ut_user_name |
3 |
C |
2 |
B |
4 |
A |
1 |
A |
数値、文字列(複数カラム)で並び替えするパターン
SELECT
*
FROM
user_tbl
ORDER BY
ut_user_id,
ut_user_name ASC;
ut_user_id |
ut_user_name |
1 |
A |
4 |
A |
2 |
B |
3 |
C |
SELECT
*
FROM
user_tbl
ORDER BY
ut_user_name DESC,
ut_user_id ASC;
ut_user_id |
ut_user_name |
4 |
A |
1 |
A |
2 |
B |
3 |
C |
GROUP BY
主にデータを集約するときに利用します
サンプルデータ
ut_user_id |
ut_user_name |
1 |
A |
2 |
B |
3 |
C |
4 |
A |
pt_product_id |
pt_product_name |
1001 |
JZA80 |
1002 |
AE86 |
1003 |
FT86 |
1004 |
BNR34 |
upt_user_id |
upt_product_id |
1 |
1001 |
1 |
1003 |
2 |
1001 |
3 |
1002 |
3 |
1004 |
4 |
1001 |
とりあえず全件表示
SELECT
upt_user_id
, ut_user_name
, upt_product_id
FROM
user_product_tbl
INNER JOIN
user_tbl ON ut_user_id = upt_user_id;
upt_user_id |
ut_user_name |
upt_product_id |
1 |
A |
1001 |
1 |
A |
1003 |
2 |
B |
1001 |
3 |
C |
1002 |
3 |
C |
1004 |
4 |
D |
1001 |
upt_user_idでグルーピング
SELECT
upt_user_id
, ut_user_name
, upt_product_id
FROM
user_product_tbl
INNER JOIN
user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id;
upt_user_id |
ut_user_name |
upt_product_id |
1 |
A |
1003 |
2 |
B |
1001 |
3 |
C |
1004 |
4 |
D |
1001 |
upt_user_id
が1つにまとまってデータ的には見やすくなりました。しかし、upt_product_id
までもが自動的にまとまってしまって、実際のデータとは異なる表示がされています。
GROUP_CONCAT
この命令を利用することでGROUP BY句によって集約されたデータを復元することができます。
SELECT
upt_user_id
, ut_user_name
, GROUP_CONCAT(upt_product_id) as 'upt_product_id '
FROM
user_product_tbl
INNER JOIN
user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id;
upt_user_id |
ut_user_name |
upt_product_id |
1 |
A |
1001,1003 |
2 |
B |
1001 |
3 |
C |
1002,1004 |
4 |
D |
1001 |
また区切り文字を変更することもできます
SELECT
upt_user_id
, ut_user_name
, GROUP_CONCAT(upt_product_id SEPARATOR '/') as upt_product_id
FROM
user_product_tbl
INNER JOIN
user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id;
upt_user_id |
ut_user_name |
upt_product_id |
1 |
A |
1001/1003 |
2 |
B |
1001 |
3 |
C |
1002/1004 |
4 |
D |
1001 |
さらにはGROUP_CONCATのなかでORDER BYを使うこともできます。何も指定していない場合にはASCがデフォルトになります。
SELECT
upt_user_id
, ut_user_name
, GROUP_CONCAT(
upt_product_id ORDER BY upt_product_id ASC
SEPARATOR '/'
) as upt_product_id
FROM
user_product_tbl
INNER JOIN
user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id;
upt_user_id |
ut_user_name |
upt_product_id |
1 |
A |
1001/1003 |
2 |
B |
1001 |
3 |
C |
1002/1004 |
4 |
D |
1001 |
HAVING
HAVING句はWHERE句と異なりGROUP BY句によってグループ化が行われたデータに対して条件を指定してデータを絞り込む場合に使用します。
SELECT
upt_user_id
, ut_user_name
, GROUP_CONCAT(
upt_product_id ORDER BY upt_product_id ASC
SEPARATOR '/'
) as products
FROM
user_product_tbl
INNER JOIN
user_tbl ON ut_user_id = upt_user_id
GROUP BY upt_user_id
HAVING COUNT(products) > 1;
upt_user_id |
ut_user_name |
products |
1 |
A |
1001/1003 |
3 |
C |
1002/1004 |
このように記述することで、GROUP_BYによってまとめられたカラムの個数に応じて取得できるデータを編集することができます。
CASE
SQL文中でも条件分岐を表現することができます。以下の書式で書かれた文法をCASE式といいます。CASE式には単純CASE式
と検索CASE式
がある。
#
CASE gender
WHEN 'man' THEN 1
WHEN 'woman' THEN 2
ELSE 99
END
#
CASE
WHEN gender = 'man' THEN 1
WHEN gender = 'woman' THEN 2
ELSE 99
END
CASE式を使うときには「条件は排他的に書くこと」「ELSEも必ず書くこと」
また、CASE式は便利ですが、下手をすると混乱を招くので以下のような書き方はやめた方がいいです。
#
CASE
WHEN gender = 'man' OR gender = 'woman' THEN 0
WHEN gender = 'man' THEN 1
WHEN gender = 'woman' THEN 2
END
#
#
CASE
WHEN gender = 'man' THEN 1
WHEN gender = 'woman' THEN 2
END
実例)フラグ
SELECT
user_id
, user_name
, product_point
, (
CASE
WHEN product_point > 80 THEN 1
ELSE 0
END
) AS high_point
FROM
user_data
ORDER BY user_id ASC
実例)階級分け
SELECT
user_id
, user_name
, product_point
, (
CASE
WHEN product_point >= 80 THEN 'S'
WHEN product_point >= 60 THEN 'A'
WHEN product_point >= 40 THEN 'B'
ELSE 'C'
END
) AS grade
FROM
user_data
ORDER BY user_id ASC
その他実例
qiita.com