【Oracle】OFFSET/FETCHで行数を限定し取得！

前回は、検索結果を並び替える ORDER BY について説明しました。今回は、行数を限定しデータを取得できる OFFSET ~ FETCH とROWNUMについて説明します！

【Oracle】ORDER BY で検索結果を並び替えよう！

前回は、重複行を除外するDISTINCTについて説明しました。今回は、検索結果を並び替える ORDER BY について説明していきます！

gptblog.jp

2024.05.14

データ準備

OFFSET ~ FETCH を試すためにテーブルを作成し、データを追加します。

--　テーブルの作成
CREATE TABLE household_account1 (
    id INT PRIMARY KEY,
    transaction_date DATE,
    expense VARCHAR2(20),
    memo VARCHAR2(80),
    deposit INT,
    withdrawal INT
);

-- データの登録
INSERT
  ALL INTO household_account1
VALUES
  (1, TO_DATE('2024-03-10', 'YYYY-MM-DD'), '給料', '3月分', 150000, 0)
INTO
  household_account1
VALUES
  (2, TO_DATE('2024-03-15', 'YYYY-MM-DD'), '食費', '昼食(スシロー)', 0, 1380) 
INTO
  household_account1
VALUES
  (3, TO_DATE('2024-03-17', 'YYYY-MM-DD'), '交通費', 'バス代', 0, 500)
INTO
   household_account1
VALUES
  (4, TO_DATE('2024-03-14', 'YYYY-MM-DD'), '食費', '夜食(とんかつ濱かつ)', 0,  2000)
INTO
   household_account1
VALUES
  (5, TO_DATE('2024-03-20', 'YYYY-MM-DD'), '通信費', 'スマホ代', 0, 3300)
INTO
   household_account1
VALUES
  (6, TO_DATE('2024-03-25', 'YYYY-MM-DD'), '交際費', 'デート', 0, 8000)
INTO
   household_account1
VALUES
  (7, TO_DATE('2024-03-21', 'YYYY-MM-DD'), '美容費', '美容室(パーマ)', 0, 10000)
INTO
   household_account1
VALUES
  (8, TO_DATE('2024-03-19', 'YYYY-MM-DD'), '交際費', '飲み会', 0, 13000)
INTO
   household_account1
VALUES
  (9, TO_DATE('2024-03-18', 'YYYY-MM-DD'), '趣味費', 'ゲーム', 0, 5000)
INTO
   household_account1
VALUES
  (10, TO_DATE('2024-03-25', 'YYYY-MM-DD'), '住居費', '3月分', 0, 55000)
SELECT
  *
FROM
  DUAL;

OFFSET ~ FETCHの使い方

前回説明した ORDER BY と一緒に使って、行数を限定して取得できるのが OFFSET ~ FETCH です。OFFSET ~ FETCH は下記のように記述します。

SELECT 列名… FROM テーブル名
　　ORDER BY 列名…
OFFSET 先頭行から除外する行数 ROWS
  FETCH NEXT 取得行数 ROWS ONLY

例えば、家計簿テーブルから3月の出金額で高かったものから順に３つ絞ってデータを取得したいと思います。

SELECT
  id AS "No",
  expense AS "費目",
  withdrawal AS "出金額"
FROM
  household_account1
ORDER BY withdrawal DESC
OFFSET 0 ROWS
  FETCH NEXT 3 ROWS ONLY

では、4~5番目の出費(出金額)を出すにはどうしたらいいのか。

ROWNUMで行数を限定してみる

MySQLでは LIMITで行数を限定して取得することができますが、OracleではLIMITが使用できません。代わりにROWNUMを使用します。 ROWNUMは下記のように記述します。

SELECT 列名… FROM テーブル名
  WHERE ROWNUM 行数 

試しに家計簿テーブルから3レコードだけ取得してみましょう。

SELECT
  id AS "No",
  expense AS "費目",
  deposit AS "入金額" ,
  withdrawal AS "出金額"
FROM
  household_account1
WHERE
  ROWNUM <= 3

OFFSET ~ FETCHの使い方 で3月の出金額で高かったものから順に３つ絞ってデータを取得しましたが、ROWNUMで取得するにはどうすればいいのか。サブクエリ(副問合せ)します。

SELECT
  *
FROM
  (
    SELECT
      id AS "No",
      expense AS "費目",
      deposit AS "入金額",
      withdrawal AS "出金額"
    FROM
      household_account1
    ORDER BY
      withdrawal DESC
  )
WHERE
  ROWNUM <= 3

これで同じデータをROWNUMを使って取得することができました。

まとめ

OFFSET ~ FETCH は行数を限定して取得でき ORDER BY と一緒に使うこと、また OFFSET ~ FETCH と同じようなことを ROWNUM を使うことで実現できるんだ。ということを覚えておきましょう！