👌

SELECTの結果をINNER JOINで使う方法

2023/09/20に公開

概要

SELECTの結果をINNER JOINで使う方法を記載します。

テーブル構造

booksとcharptersを1対多で定義しています。

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT
);

CREATE TABLE charpters (
  id INTEGER PRIMARY KEY,
  sort_number INTEGER,
  name TEXT,
  book_id INTEGER,
  UNIQUE(book_id, sort_number),
  FOREIGN KEY (book_id) REFERENCES books(id)
);

最初の章を取得する

SELECT
    books.title,
    min_sort_number || "章",
    charpter_name
FROM
    books
    INNER JOIN (
        SELECT
            book_id,
            MIN(sort_number) min_sort_number,
            name charpter_name
        FROM
            charpters
        GROUP BY
            book_id
    ) t ON books.id = t.book_id;

クエリを実行した結果となります。

夜行|1章|尾道
玩具修理者|1章|玩具修理者

最後の章を取得する

SELECT
    books.title,
    max_sort_number || "章",
    charpter_name
FROM
    books
    INNER JOIN (
        SELECT
            book_id,
            MAX(sort_number) max_sort_number,
            name charpter_name
        FROM
            charpters
        GROUP BY
            book_id
    ) t ON books.id = t.book_id;

クエリを実行した結果となります。

夜行|5章|鞍馬
玩具修理者|2章|酔歩する男

最初と最後の章を取得する

SELECT
    books.title,
    min_sort_number || "章" || min_charpter_name,
    max_sort_number || "章" || max_charpter_name
FROM
    books
    INNER JOIN (
        SELECT
            book_id,
            MIN(sort_number) min_sort_number,
            name min_charpter_name
        FROM
            charpters
        GROUP BY
            book_id
    ) t1 ON books.id = t1.book_id
    INNER JOIN (
        SELECT
            book_id,
            MAX(sort_number) max_sort_number,
            name max_charpter_name
        FROM
            charpters
        GROUP BY
            book_id
    ) t2 ON books.id = t2.book_id;

クエリを実行した結果となります。

夜行|1章尾道|5章鞍馬
玩具修理者|1章玩具修理者|2章酔歩する男

以上です。

Discussion