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

Oracle

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

データ準備

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;
日付費目メモ入金額出金額
2024/03/10給料3月分1500000
2024/03/15食費昼食(スシロー)01380
2024/03/17交通費バス代0500
2024/03/14食費夜食(とんかつ濱かつ)02000
2024/03/20通信費スマホ代03300
2024/03/25交際費デート08000
2024/03/21美容費美容室(パーマ)010000
2024/03/19交際費飲み会013000
2024/03/18趣味費ゲーム05000
2024/03/25住居費3月分055000
家計簿テーブル

OFFSET ~ FETCHの使い方

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

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

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

SELECT
  id AS "No",
  expense AS "費目",
  withdrawal AS "出金額"
FROM
  household_account1
ORDER BY withdrawal DESC
OFFSET 0 ROWS
  FETCH NEXT 3 ROWS ONLY
No費目出金額
10住居費55000
8交際費13000
7美容費10000
出費のトップ3

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

No費目出金額
6交際費8000
9趣味費5000
出費の原因 4番目、5番目を取得
OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY

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
No費目入金額出金額
1給料1500000
2食費01380
3交通費0500
家計簿テーブルから3レコードだけ取得

OFFSET ~ FETCHの使い方3月の出金額で高かったものから順に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を使って取得することができました。

No費目入金額出金額
10住居費055000
8交際費013000
7美容費010000
出費のトップ3

まとめ

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

コメント