#はじめに
こちらの記事で紹介した、テーブルの抽出条件に、分析関数(ウインドウ関数)を使用する方法の応用になります。
分析関数はDBMSによっては使えない場合があるのでご注意ください。
こちらの記事は、
MySQL 8.0.20 にて動作を確認しています。
#汎用的なひな型
特定の条件を満たすレコードを1件でも持つグループのレコードをすべて抽出するSQLのひな型
SELECT *
FROM
(SELECT *
, MAX(CASE WHEN [条件] THEN 1 END)
OVER (PARTITION BY [グループ化項目]) AS 抽出対象
FROM 成績テーブル
) AS Z
WHERE 抽出対象 = 1
SELECT * の部分は便宜必要なカラムに変更して実行してください
条件
[条件]のところに抽出したい条件を記述
グループ化項目
[グループ化項目]のところにグループ化したいカラムを記述(複数カラムの指定可)
#例題
成績テーブルのうち、0点または100点を1科目でも取った生徒のすべての教科の点数を一覧で取得する
成績テーブル
氏名 | 教科 | 点数 |
---|---|---|
佐藤 | 国語 | 0 |
佐藤 | 数学 | 31 |
佐藤 | 英語 | 19 |
鈴木 | 国語 | 59 |
鈴木 | 数学 | 38 |
鈴木 | 英語 | 25 |
高橋 | 国語 | 92 |
高橋 | 数学 | 100 |
高橋 | 英語 | 39 |
田中 | 国語 | 40 |
田中 | 数学 | 52 |
田中 | 英語 | 30 |
抽出結果
佐藤さんと高橋さんのデータをすべて抽出する
氏名 | 教科 | 点数 |
---|---|---|
佐藤 | 国語 | 0 |
佐藤 | 数学 | 31 |
佐藤 | 英語 | 19 |
高橋 | 国語 | 92 |
高橋 | 数学 | 100 |
高橋 | 英語 | 39 |
##テストデータ作成
CREATE TABLE 成績テーブル(氏名 VARCHAR(10), 教科 VARCHAR(2), 点数 INT);
INSERT INTO 成績テーブル VALUES('佐藤', '国語', 0);
INSERT INTO 成績テーブル VALUES('佐藤', '数学', 31);
INSERT INTO 成績テーブル VALUES('佐藤', '英語', 19);
INSERT INTO 成績テーブル VALUES('鈴木', '国語', 59);
INSERT INTO 成績テーブル VALUES('鈴木', '数学', 38);
INSERT INTO 成績テーブル VALUES('鈴木', '英語', 25);
INSERT INTO 成績テーブル VALUES('高橋', '国語', 92);
INSERT INTO 成績テーブル VALUES('高橋', '数学', 100);
INSERT INTO 成績テーブル VALUES('高橋', '英語', 39);
INSERT INTO 成績テーブル VALUES('田中', '国語', 40);
INSERT INTO 成績テーブル VALUES('田中', '数学', 52);
INSERT INTO 成績テーブル VALUES('田中', '英語', 30);
##対象データの抽出
SELECT 氏名, 教科, 点数
FROM
(SELECT *
, MAX(CASE WHEN 点数=0 OR 点数=100 THEN 1 END)
OVER (PARTITION BY 氏名) AS 抽出対象
FROM 成績テーブル
) AS Z
WHERE 抽出対象 = 1
サブクエリ部分の実行結果
氏名 | 教科 | 点数 | 抽出対象 |
---|---|---|---|
佐藤 | 国語 | 0 | 1 |
佐藤 | 数学 | 31 | 1 |
佐藤 | 英語 | 19 | 1 |
田中 | 国語 | 40 | « NULL » |
田中 | 数学 | 52 | « NULL » |
田中 | 英語 | 30 | « NULL » |
鈴木 | 国語 | 59 | « NULL » |
鈴木 | 数学 | 38 | « NULL » |
鈴木 | 英語 | 25 | « NULL » |
高橋 | 国語 | 92 | 1 |
高橋 | 数学 | 100 | 1 |
高橋 | 英語 | 39 | 1 |
全体の実行結果
氏名 | 教科 | 点数 |
---|---|---|
佐藤 | 国語 | 0 |
佐藤 | 数学 | 31 |
佐藤 | 英語 | 19 |
高橋 | 国語 | 92 |
高橋 | 数学 | 100 |
高橋 | 英語 | 39 |
こちらでお試しできます