SQLパズル55 競走馬の入賞回数 の 解法3の学習メモ

概要

SQLパズル　p230 55 競走馬の入賞回数 の 解法3の学習メモです。

内容は、サブクエリ使っているだけですね。
理解しやすい解法でした。

要件

３つのカラムから集計値を求める。

今回の場合だと、3つのカラムwin_name,place_name,show_nameがある。
３つのカラムとも馬の名前が入っており　３つのカラムに対して馬の名前の集計値を取得したい。

SQL

DDL

--  p228 55 競走馬の入賞回数
DROP TABLE IF EXISTS RacingResults;
CREATE TABLE RacingResults
(track_id CHAR(3) NOT NULL,
 race_date DATE NOT NULL,
 race_nbr INTEGER NOT NULL,
 win_name CHAR(30) NOT NULL,
 place_name CHAR(30) NOT NULL,
 show_name CHAR(30) NOT NULL,
    PRIMARY KEY (track_id, race_date, race_nbr));

DROP TABLE IF EXISTS HorseNames;
CREATE TABLE HorseNames
(horse CHAR(30) NOT NULL PRIMARY KEY);

INSERT INTO RacingResults VALUES(1, '2007-05-01', 1, 'A', 'B', 'C');
INSERT INTO RacingResults VALUES(1, '2007-05-01', 2, 'E', 'F', 'P');
INSERT INTO RacingResults VALUES(1, '2007-05-02', 1, 'B', 'C', 'A');
INSERT INTO RacingResults VALUES(2, '2007-05-02', 1, 'O', 'P', 'Q');
INSERT INTO RacingResults VALUES(2, '2007-05-02', 2, 'A', 'P', 'Q');

INSERT INTO HorseNames VALUES('A');
INSERT INTO HorseNames VALUES('B');
INSERT INTO HorseNames VALUES('C');
INSERT INTO HorseNames VALUES('D');
INSERT INTO HorseNames VALUES('E');
INSERT INTO HorseNames VALUES('F');
INSERT INTO HorseNames VALUES('O');
INSERT INTO HorseNames VALUES('P');
INSERT INTO HorseNames VALUES('Q');

答えのSQL

-- 55 競走馬の入賞回数　その３　p230
SELECT H1.horse,
        (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.win_name = H1.horse)
      + (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.place_name = H1.horse)
      + (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.show_name = H1.horse)
    FROM HOrseNames AS H1;

-- 途中式　各々の項目を表示するだけ
SELECT H1.horse,
        (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.win_name = H1.horse) as C1,
      (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.place_name = H1.horse) as C2,
       (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.show_name = H1.horse) as C3,
        (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.win_name = H1.horse)
      + (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.place_name = H1.horse)
      + (SELECT COUNT(*) 
           FROM RacingResults AS R1
              WHERE R1.show_name = H1.horse)
    FROM HOrseNames AS H1;

参考

SQLパズル　p230

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p55

達人に学ぶSQL徹底指南書第２版　該当する場所なし。

github差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/5e06eb1fcf0548c87e4560d340eddc7556ca136d