1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

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

Last updated at Posted at 2020-09-12

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

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

こちらでお試しできます

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?