TOP書籍連動> 結果セットにおける行番号の指定




まるごと PostgreSQL!
PostgreSQLとOracleによるデータベース相互移行マニュアル

第4回:SQL文の移行(1)
著者:奥畑 裕樹(OKUHATA, Hiroki)   2005/10/27
1   2  次のページ
結果セットにおける行番号の指定

   Oracleでは、結果セットの特定の行だけを表示するために、ROWNUM疑似列およびLINEBETWEEN条件が使えます。次のSQL文はROWNUMを用いた例です。
SELECT * FROM 商品マスタ
  WHERE 卸単価 IS NOT NULL
  AND ROWNUM <= 5
  ORDER BY 卸単価 DESC;
   PostgreSQLには、等価な機能を実現できるOFFSET、LIMITがあるので、表4のような組み合わせで変換します。

Oracle   PostgreSQL
ROWNUM ←→ OFFSET
LINE BETWEEN ←→ OFFSET + LIMIT

表4:特定行の表示機能の変換

   ただし、PostgreSQLのLIMIT、OFFSETはWHERE句の一部ではないので、ROWNUMを削除するだけではなく、WHERE句に条件の指定がない場合には、WHERE句も削除する必要があるので注意が必要です。

SELECT * FROM 商品マスタ
   WHERE 卸単価 IS NOT NULL
   ORDER BY 卸単価 DESC
   LIMIT 5 OFFSET 0;

   図4は、このSQL文の実行結果です。

商品id | 商品名 | グループ名 | 仕入単価 | 卸単価
-------- + ------------------------ + -------------- + ---------- + --------
|  ノート型パソコン |  パソコン本体 | 230000  | 270000 
|  デスクトップ型パソコン |  パソコン本体 | 150000  | 180000 
|  15型_液晶ディスプレイ |  周辺機器 | 100000  | 120000 
|  19型_ディスプレイ |  周辺機器 | 80000  | 95000 
|  17型_ディスプレイ |  周辺機器 | 40000  | 50000 
(5 rows)

図4:「LIMIT/OFFSET」を利用した実行結果


OFFSET + LIMIT を LINE BETWEENで実現する

   例えば、下記のようなPostgreSQL用のクエリがあったとします。これは、結果を2行捨てて3行目から3行分表示させるものです。

SELECT 商品ID, 商品名, 卸単価 FROM 商品マスタ
     WHERE 卸単価 IS NOT NULL
     ORDER BY 卸単価 DESC
     LIMIT 3 OFFSET 2;

   Oracleで同じことをするには、下記のようにROWNUM擬似列を使い、lineのような別名の列にし、それをBETWEEN句で絞る方法が使えます。

SELECT *
     FROM (SELECT 商品ID, 商品名, 卸単価, ROWNUM line
          FROM (SELECT * FROM 商品マスタ
               WHERE 卸単価 IS NOT NULL
               ORDER BY 卸単価 DESC ))
     WHERE line BETWEEN 3 AND 5;

   上記のテクニックは、若干「むりやり感」がありますが、これは下記のように単純にROWNUMをBETWEEN句で用いてしまうと、期待する結果が得られないためです。

SELECT * FROM 商品マスタ
     WHERE 卸単価 IS NOT NULL
          AND ROWNUM BETWEEN 3 AND 5
     ORDER BY 卸単価 DESC

レコードが選択されませんでした。

アウタージョインの構文の違い

   Oracle、PostgreSQLともにOUTER JOINの機能がありますが、構文が違いますのでOracleの(+)を使った記法を次のように変更する必要があります。

Oracleのアウタージョインの構文
SELECT B.商品名, SUM(A.数量)
  FROM 売上データA, 商品マスタB
  WHERE A.商品ID (+)= B.商品ID
  GROUP BY B.商品名;

PostgreSQLのアウタージョインの構文
SELECT B.商品名,
         SUM(A.数量) AS "SUM(A.数量)"
    FROM 売上データA
         RIGHT OUTER JOIN
         商品マスタB
ON A.商品ID = B.商品ID
    GROUP BY B.商品名;
   図5はその実行結果です。

商品名 | SUM(A.数量)
-------------------------------- + -------------
15型_液晶ディスプレイ | 6
17型_ディスプレイ | 5
19型_ディスプレイ | 3
HUB | 5
スキャナ | 1
ターミナルアダプタ | 2
デジタルカメラ |  
デスクトップ型パソコン | 6
ノート型パソコン | 8
プリンタ | 1
(10 rows)

図5:アウタージョインの実行結果

1   2  次のページ

書籍紹介
まるごと PostgreSQL! Vol.1
まるごと PostgreSQL! Vol.1 日本での市場シェアNo.1(オープンソースRDBMS部門)データベースソフトPostgreSQLの最新情報を、開発メンバーを含む豪華執筆陣が多面的かつ詳細に解説します。PostgreSQL用クラスタリング/レプリケーションソフト、PostgreSQL⇔Oracle移行、PostgreSQLによる大規模サイト構築法、新バージョンPostgreSQL 8.0先行レビュー、PostgreSQL用.NETデータプロバイダ等々、他では読めない貴重な記事が満載です。

発売日:2004/12/08発売
定価:\1,890(本体 \1,800+税)
奥畑 裕樹(OKUHATA, Hiroki)
著者プロフィール
奥畑 裕樹(OKUHATA, Hiroki)
Javaとオープンソース技術を得意とする技術コンサルタント。最近のテーマは、ソフトウェア開発の全体最適をはかること。気が付けば、10才のときにプログラミングを始めて以来、常に何かを作っている…。


この記事の評価をお聞かせください
ボタンをクリックしますとウインドウが開きます。

INDEX
第4回:SQL文の移行(1)
結果セットにおける行番号の指定
  副問い合わせ
PostgreSQLとOracleによるデータベース相互移行マニュアル
第1回 データベース移行
第2回 データの移行(1)
第3回 データの移行(2)
第4回 SQL文の移行(1)
第5回 SQL文の移行(2)
第6回 SSQL文の移行(3)
第7回 ファンクションの移行(1)
第8回 ファンクションの移行(2)
第9回 ストアドプロシージャの移行(1)
第10回 ストアドプロシージャの移行(2)
第11回 ストアドプロシージャの移行(3)
第12回 ストアドプロシージャの移行(4)
第13回 まとめ