SQLで特定の条件を満たすレコードを1件でも持つグループのレコードをすべて抽出する

はじめに

こちらの記事で紹介した、テーブルの抽出条件に、分析関数（ウインドウ関数）を使用する方法の応用になります。

分析関数は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

こちらでお試しできます