SQL – SELECTで絞り込んだものに、もうひと工夫加えてみる｜Chopesu Blog - 文系・未経験でエンジニアに転身した元営業マンのエンジニアライフ

SELECT文を使って、行を絞り込んだのはいいけど、例えば「値段が高い順に表示させたい」「日付が最新順になるように表示させたい」とか色々とやりたいことが出てくる。そんなときに、検索結果を自分の思い通りに加工して表示させることができる。

Contents

加工するための文一覧
例文
集合演算子
ビュー
- 記事の内容を共有・保存する
- その他によく読まれている記事

加工するための文一覧

文名	できること
DISTINCT	検索結果から重複した行を除外したいときに使う
ORDER BY	検索結果の順序を並び替える
OFFSET – FETCH	検索結果から件数を限定して（ex.3件だけ）取得したいときに使う
UNION	検索結果にほかの検索結果を足し合わせる（和集合）
EXCEPT	検索結果からほかの検索結果を差し引く（差集合）
INTERSECT	検索結果とほかの検索結果で重複する部分を取得する（積集合）

例文

#重複除外（家計簿を例に取ると、支出したお金の用途の種類の一覧を抽出したいときに使う）
SELECT
DISTINCT 支出用途  -- 支出用途の中で重複しているものを除外する
FROM 家計簿


#並び替え
SELECT *
FROM 家計簿
ORDER BY 日付 DESC  -- 日付降順（直近から過去に遡る）で表示

SELECT *
FROM 家計簿
ORDER BY 日付 ASC  -- 日付昇順（過去から直近に）で表示


#複数の列で並び替え
SELECT *
FROM 家計簿
ORDER BY 収入 DESC, 支出 DESC  -- 収入を降順に並び替えたものを、さらに支出の降順で並び替え


#先頭から数行だけ取得（金額が高い順に3件を取得したいときとか、ランキング表示したいときとかに便利かも）
##出金額の高い順に3件だけ取得
SELECT 費目, 出金額
FROM 家計簿
ORDER BY 出金額 DESC
OFFSET 0 ROWS  -- 1件目から取得したいので、インデックスで指定（0 = 1番目）
FETCH NEXT 3 ROWS ONLY  -- 3件（行）だけ取得

##3番目に高い出金額だけを取得
SELECT 費目, 出金額
FROM 家計簿
ORDER BY 出金額 DESC
OFFSET 2 ROWS  -- 3番目を取得したいので、インデックスで指定（2 = 3番目）
FETCH NEXT 1 ROWS ONLY  -- 1件（行）だけ取得

#重複除外（家計簿を例に取ると、支出したお金の用途の種類の一覧を抽出したいときに使う）

SELECT

DISTINCT 支出用途 -- 支出用途の中で重複しているものを除外する

FROM 家計簿

#並び替え

SELECT *

FROM 家計簿

ORDER BY 日付 DESC -- 日付降順（直近から過去に遡る）で表示

SELECT *

FROM 家計簿

ORDER BY 日付 ASC -- 日付昇順（過去から直近に）で表示

#複数の列で並び替え

SELECT *

FROM 家計簿

ORDER BY 収入 DESC, 支出 DESC -- 収入を降順に並び替えたものを、さらに支出の降順で並び替え

#先頭から数行だけ取得（金額が高い順に3件を取得したいときとか、ランキング表示したいときとかに便利かも）

##出金額の高い順に3件だけ取得

SELECT 費目, 出金額

FROM 家計簿

ORDER BY 出金額 DESC

OFFSET 0 ROWS -- 1件目から取得したいので、インデックスで指定（0 = 1番目）

FETCH NEXT 3 ROWS ONLY -- 3件（行）だけ取得

##3番目に高い出金額だけを取得

SELECT 費目, 出金額

FROM 家計簿

ORDER BY 出金額 DESC

OFFSET 2 ROWS -- 3番目を取得したいので、インデックスで指定（2 = 3番目）

FETCH NEXT 1 ROWS ONLY -- 1件（行）だけ取得

集合演算子

UNION、EXCEPT、INTERSECTは集合演算子と言われる
https://tech.nikkeibp.co.jp/it/article/COLUMN/20070802/278932/

例えば、1月の家計簿と2月の家計簿を別々のテーブルを作って作成していた場合。
1月と2月の家計簿を一覧表示で見たいとき等に、集合演算子があると便利

#和集合（1月と2月の家計簿を足して一覧表示）
SELECT 支出用途, 収入, 支出 FROM 1月の家計簿
UNION
SELECT 支出用途, 収入, 支出 FROM 2月の家計簿

#差集合（1月の家計簿にある支出用途から2月の家計簿の支出用途と重複した部分を取り除いて表示。つまり1月には支出したが2月には支出しなかった用途が分かる）
SELECT 支出用途 FROM 1月の家計簿
EXCEPT
SELECT 支出用途 FROM 2月の家計簿

#積集合（1月の家計簿にある支出用途と2月の家計簿の支出用途の重複した部分を表示）
SELECT 支出用途 FROM 1月の家計簿
INTERSECT
SELECT 支出用途 FROM 2月の家計簿

#和集合（1月と2月の家計簿を足して一覧表示）

SELECT 支出用途, 収入, 支出 FROM 1月の家計簿

UNION

SELECT 支出用途, 収入, 支出 FROM 2月の家計簿

#差集合（1月の家計簿にある支出用途から2月の家計簿の支出用途と重複した部分を取り除いて表示。つまり1月には支出したが2月には支出しなかった用途が分かる）

SELECT 支出用途 FROM 1月の家計簿

EXCEPT

SELECT 支出用途 FROM 2月の家計簿

#積集合（1月の家計簿にある支出用途と2月の家計簿の支出用途の重複した部分を表示）

SELECT 支出用途 FROM 1月の家計簿

INTERSECT

SELECT 支出用途 FROM 2月の家計簿

この集合演算子は、アプリケーション開発においてデータベースに保存した別々のテーブルに存在するデータを使って分析したり、集計したりしたいときに使えそうな感じがする。

和集合の例文を見ると分かりやすいが、集合演算子はSELECT文で選択した列の数とそれぞれのデータ型が一致していないと使えない。例えば、下記のようなUNIONは使えない

SELECT 支出用途, 収入, 支出 FROM 1月の家計簿
UNION
SELECT 日付, 収入 FROM 2月の家計簿

→SELECTの列数が異なるし、データ型も異なる

SELECT 支出用途, 収入, 支出 FROM 1月の家計簿

UNION

SELECT 日付, 収入 FROM 2月の家計簿

→SELECTの列数が異なるし、データ型も異なる

ビュー

SQLを書いていると、「あれ？また同じSQL文書かかなあかんの？だるっ！」となる時がある。こういうときにはSQL文で書いた処理を一つのテンプレートにした「ビュー」という機能を使うと便利。
ビューはRailsやDjangoでいう「render」をイメージすると理解しやすいかもしれない。

CREATE VIEW 家計簿1月 AS  -- ビューを作成(CREATE VIEW)
SELECT *
FROM 家計簿
WHERE 日付 >= '2019-01-01'
　　　　日付 <= '2019-01-31'
-------------------------------------------
SELECT *
FROM 家計簿1月;　　　　　　-- ビューの呼び出し
SELECT DISTINCT 支出用途 FROM 家計簿1月;