作成日
最終更新
記事区分
運用時などに必要になるけれど、使用頻度が低く理解が曖昧になりがちな SQL をチートシート化しておきます。
重複を省いて SELECT (distinct)
SELECT 時
SELECT DISTINCT columnname FROM tablename
COUNT との併用
SELECT COUNT(DISTINCT columnname) FROM tablename
同じようなテーブルを結合してから SELECT (union)
サブクエリの結果を結合して、一時的なテーブルを作成する際に重宝します。
- UNION → 重複レコードは取得しない
- UNION ALL → 重複レコードも取得する
UNION ALL で同じようなテーブルを結合して columnname の個数を集計する例
SELECT columnname, COUNT(*) FROM
(
SELECT columnname FROM tablename1 UNION ALL
SELECT columnname FROM tablename2
) AS cname
GROUP BY columnname
テーブルを結合 (SELECT 結果を INSERT)
tablename2 と tablename3 を結合して、何らかの用途で必要になった tablename1 を作成する例
INSERT INTO tablename1 (SELECT columnname1, columnnam2 FROM tablename2)
INSERT INTO tablename1 (SELECT columnname1, columnnam2 FROM tablename3)
特定の GROUP のみを表示
レコードは WHERE で条件指定しますが、グループ (GROUP BY した結果) は HAVING で条件指定します。
SELECT columnname, COUNT(*) FROM tablename GROUP BY columnname HAVING COUNT(*) > 1
JOIN の使い分け
tablename1
a 1
b 1
c 2
tablename2
1 D
3 E
LEFT JOIN
tablename1 に tablename2 を左外部結合 (LEFT JOIN、LEFT OUTER JOIN)
a 1 D
b 1 D
c 2 NULL
RIGHT JOIN
tablename1 に tablename2 を右外部結合 (RIGHT JOIN、RIGHT OUTER JOIN)
a 1 D
b 1 D
NULL 3 E
FULL OUTER JOIN
tablename1 と tablename2 を完全外部結合 (FULL OUTER JOIN)
a 1 D
b 1 D
c 2 NULL
NULL 3 E
INNER JOIN
tablename1 と tablename2 を内部結合 (INNER JOIN)
a 1 D
b 1 D
単に JOIN と記載すると INNER JOIN になります。結合される二つのテーブルのどちらかにしか存在しないレコードは抽出されません。
CROSS JOIN
外積と内積に加えて直積 (CROSS JOIN) もありますが、実運用で使用することはあまりありません。直積の性質上、外積や内積の場合と異なり ON などで結合条件を指定しません。
SELECT * FROM tablename1 AS t1 CROSS JOIN tablename2 AS t2
結合結果のレコード数は「tablename1 のレコード数 * tablename2 のレコード数」となります。
a 1 1 D
a 1 3 E
b 1 1 D
b 1 3 E
c 2 1 D
c 2 3 E
補足
同じテーブル同士を結合することもできます。自己結合とよびます。テーブル名を区別できないため AS によるエイリアスが必要です。
SELECT alias1.id, alias2.id FROM tablename AS alias1 LEFT JOIN tablename AS alias2 ON alias1.id = alias2.id
ウィンドウ関数
サンプルデータ
CREATE TABLE tbl (c1 VARCHAR(255), c2 VARCHAR(255));
INSERT INTO tbl (c1, c2) VALUES ('a','a'),('b','b'),('c','c'),('a','aa'),('b','bb'),('c','cc'),('a','aaa'),('b','bbb'),('c','ccc');
OVER に何も指定しないとウィンドウは一つです。
SELECT *, COUNT(*) OVER() FROM tbl;
+------+------+-----------------+
| c1 | c2 | COUNT(*) OVER() |
+------+------+-----------------+
| a | a | 9 |
| b | b | 9 |
| c | c | 9 |
| a | aa | 9 |
| b | bb | 9 |
| c | cc | 9 |
| a | aaa | 9 |
| b | bbb | 9 |
| c | ccc | 9 |
+------+------+-----------------+
ウィンドウを分割するためには PARTITION BY を指定します。
SELECT *, COUNT(*) OVER(PARTITION BY c1) FROM tbl;
+------+------+--------------------------------+
| c1 | c2 | COUNT(*) OVER(PARTITION BY c1) |
+------+------+--------------------------------+
| a | a | 3 |
| a | aa | 3 |
| a | aaa | 3 |
| b | b | 3 |
| b | bb | 3 |
| b | bbb | 3 |
| c | c | 3 |
| c | cc | 3 |
| c | ccc | 3 |
+------+------+--------------------------------+
ウィンドウ内の各行を ORDER BY でソートすると集約対象がウィンドウ全体ではなく現在行までになります。この集約対象をフレームとよびます。
SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) FROM tbl;
+------+------+--------------------------------------------+
| c1 | c2 | COUNT(*) OVER(PARTITION BY c1 ORDER BY c2) |
+------+------+--------------------------------------------+
| a | a | 1 |
| a | aa | 2 |
| a | aaa | 3 |
| b | b | 1 |
| b | bb | 2 |
| b | bbb | 3 |
| c | c | 1 |
| c | cc | 2 |
| c | ccc | 3 |
+------+------+--------------------------------------------+
各ウィンドウにおけるフレームは、既定でウィンドウ内の最初の行から現在行までですが、これを変更することができます。PRECEDING と FOLLOWING の片方だけ指定する場合は BETWEEN を省略できます。また、特殊な指定方法として、ウィンドウ内の先頭行 UNBOUNDED PRECEDING、最終行 UNBOUNDED FOLLOWING、現在行 CURRENT ROW があります。
SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) FROM tbl;
SELECT *, COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
ROWS 1 PRECEDING) FROM tbl;
+------+------+---------------------------------------------------------------------------------------+
| c1 | c2 | COUNT(*) OVER(PARTITION BY c1 ORDER BY c2
ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) |
+------+------+---------------------------------------------------------------------------------------+
| a | a | 1 |
| a | aa | 2 |
| a | aaa | 2 |
| b | b | 1 |
| b | bb | 2 |
| b | bbb | 2 |
| c | c | 1 |
| c | cc | 2 |
| c | ccc | 2 |
+------+------+---------------------------------------------------------------------------------------+
代表的なウィンドウ関数
ROW_NUMBER()
ウィンドウ内における連番を付与できます。
SELECT *,
ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2 DESC)
FROM tbl
ORDER BY c2;
+------+------+-----------------------------------------------------+
| c1 | c2 | ROW_NUMBER() OVER(PARTITION BY c1 ORDER BY c2 DESC) |
+------+------+-----------------------------------------------------+
| a | a | 3 |
| a | aa | 2 |
| a | aaa | 1 |
| b | b | 3 |
| b | bb | 2 |
| b | bbb | 1 |
| c | c | 3 |
| c | cc | 2 |
| c | ccc | 1 |
+------+------+-----------------------------------------------------+
RANK()、DENSE_RANK()
ウィンドウ内における ORDER BY でソートしたときの順位を付与できます。以下では PARTITION BY を指定していないためウィンドウは一つです。DENSE_RANK() は同順を詰めて順位付けします。
SELECT *,
RANK() OVER(ORDER BY c1),
DENSE_RANK() OVER(ORDER BY c1)
FROM tbl;
+------+------+--------------------------+--------------------------------+
| c1 | c2 | RANK() OVER(ORDER BY c1) | DENSE_RANK() OVER(ORDER BY c1) |
+------+------+--------------------------+--------------------------------+
| a | a | 1 | 1 |
| a | aa | 1 | 1 |
| a | aaa | 1 | 1 |
| b | b | 4 | 2 |
| b | bb | 4 | 2 |
| b | bbb | 4 | 2 |
| c | c | 7 | 3 |
| c | cc | 7 | 3 |
| c | ccc | 7 | 3 |
+------+------+--------------------------+--------------------------------+
OVER が複数ある場合は WINDOW で作成したエイリアスを利用するとクエリが綺麗になります。
SELECT *,
RANK() OVER(w),
DENSE_RANK() OVER(w)
FROM tbl
WINDOW
w AS (ORDER BY c1);
RANK() の応用例として、各ウィンドウ内で一位の行を抜き出すことができます。
SELECT t.* FROM
(SELECT *,
RANK() OVER(w) AS rnk,
DENSE_RANK() OVER(w) AS drnk
FROM tbl
WINDOW
w AS (ORDER BY c1)) AS t
WHERE
t.rnk = 1;
+------+------+-----+------+
| c1 | c2 | rnk | drnk |
+------+------+-----+------+
| a | a | 1 | 1 |
| a | aa | 1 | 1 |
| a | aaa | 1 | 1 |
+------+------+-----+------+
LAG()、LEAD()、FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()
ウィンドウ内の現在行から前 LAG() 後 LEAD() に1行ずれた行の値を取得できます。
SELECT *,
LAG(c2, 1) OVER(w),
LEAD(c2, 1) OVER(w)
FROM tbl
WINDOW
w AS (PARTITION BY c1 ORDER BY c2);
+------+------+--------------------+---------------------+
| c1 | c2 | LAG(c2, 1) OVER(w) | LEAD(c2, 1) OVER(w) |
+------+------+--------------------+---------------------+
| a | a | NULL | aa |
| a | aa | a | aaa |
| a | aaa | aa | NULL |
| b | b | NULL | bb |
| b | bb | b | bbb |
| b | bbb | bb | NULL |
| c | c | NULL | cc |
| c | cc | c | ccc |
| c | ccc | cc | NULL |
+------+------+--------------------+---------------------+
ウィンドウ内の最初の行、最後の行、ある指定した行の値を取得できます。
SELECT *,
FIRST_VALUE(c2) OVER(w),
LAST_VALUE(c2) OVER(w),
NTH_VALUE(c2, 2) OVER(w)
FROM tbl
WINDOW
w AS (PARTITION BY c1 ORDER BY c2);
+------+------+-------------------------+------------------------+--------------------------+
| c1 | c2 | FIRST_VALUE(c2) OVER(w) | LAST_VALUE(c2) OVER(w) | NTH_VALUE(c2, 2) OVER(w) |
+------+------+-------------------------+------------------------+--------------------------+
| a | a | a | aaa | aa |
| a | aa | a | aaa | aa |
| a | aaa | a | aaa | aa |
| b | b | b | bbb | bb |
| b | bb | b | bbb | bb |
| b | bbb | b | bbb | bb |
| c | c | c | ccc | cc |
| c | cc | c | ccc | cc |
| c | ccc | c | ccc | cc |
+------+------+-------------------------+------------------------+--------------------------+
0
記事の執筆者にステッカーを贈る
有益な情報に対するお礼として、またはコメント欄における質問への返答に対するお礼として、 記事の読者は、執筆者に有料のステッカーを贈ることができます。
さらに詳しく →
Feedbacks
ログインするとコメントを投稿できます。
関連記事
- MySQL レプリケーション設定 (2段, 3段)MySQL DB サーバは自身へのクエリをバイナリログとして書き出すことができます。レプリケーションとは、追加の MySQL DB サーバが、別の MySQL DB サーバが出力したバイナリログを自分のリレーログとよばれるログにコピーして、更にリレーログに記載されたクエリを自分自身のテーブルに実行する機能です。バイナリログを出力する DB をマスターとよび、自分のリレーログにコピーする DB をス...
- MySQL 5.7 インストール手順2016/11/07 時点、多くの環境では yum レポジトリ等に mysql 5.7 が含まれていません。公式ページからダウンロードしてインストールする手順を二つまとめます。 yum レポジトリを追加する手順 [Installing MySQL on Linux Using the MySQL Yum Repository (mysql 5.7)](http://dev.mysql.com/do...
- MyBatis 3 サンプルコード (Java/MySQL/Gradle)MyBatis は、JDBC を直接利用せずに、XML ファイル等で用意した SQL 文を利用して DB にアクセスするための Java ライブラリです。内部的には JDBC が利用されているため、JDBC のインストールは必要です。同様のライブラリに Hibernate ORM があります。DB は特に MySQL を対象として、MyBatis バージョン 3 の
- MySQL HandlerSocket Plugin の簡単な使用方法HandlerSocket は MySQL プロセス内のスレッドとして動作する、MySQL の NoSQL フロントエンドです。独自のプロトコルを用いて TCP 通信を行います。SQL 構文の解析が不要、プロトコルがシンプルなどの理由によって、単純な DB アクセスが高速に実行可能になります。MariaDB であれば[標準プラグインとして付属](https://mariadb.co
- Spring Boot から MyBatis を利用するための設定 (Gradle/MySQL)MyBatis を Spring Boot で利用するための基本的な設定およびサンプルコードをまとめます。サンプルコードにおいては、特に MySQL を対象とします。 MyBatis Spring-Boot-Starter チュートリアル
