第4回：SQL文の移行（1） (1/2)

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

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

第４回：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`	`\|`	`商品名`	`\|`	`グループ名`	`\|`	`仕入単価`	`\|`	`卸単価`
`--------`	`+`	`------------------------`	`+`	`--------------`	`+`	`----------`	`+`	`--------`
`2`	`\|`	`ノート型パソコン`	`\|`	`パソコン本体`	`\|`	`230000`	`\|`	`270000`
`1`	`\|`	`デスクトップ型パソコン`	`\|`	`パソコン本体`	`\|`	`150000`	`\|`	`180000`
`5`	`\|`	`15型_液晶ディスプレイ`	`\|`	`周辺機器`	`\|`	`100000`	`\|`	`120000`
`4`	`\|`	`19型_ディスプレイ`	`\|`	`周辺機器`	`\|`	`80000`	`\|`	`95000`
`3`	`\|`	`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

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

発売日：2004/12/08発売
定価：\1,890（本体 \1,800＋税）

著者プロフィール
奥畑裕樹（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回	まとめ

人気記事ランキング

人気コーナーランキング

タイプ

連載

特集

人気記事トップ10

新着記事

企画広告も役立つ情報バッチリ！ Sponsored