前回の続きです。
Db2 : FETCH FIRST n ROWSとOFFSET、およびLIMIT代替構文
Db2 11.1では、PostgreSQLやMySQLなどで使用されるLIMIT ... OFFSETといった代替構文も使用できるようになりました。
副選択(subselect)でFETCH FIRSTとOFFSETを使う
FETCH FIRST/OFFSET節を使って SELECTでn行までしか取得しないように限定する書き方ですが、副選択(subselect)照会で使うこともできます。
こちらはDb2のSAMPLEデータベースのEMPLOYEE表を使っています。
例えば、
「３番目にサラリー平均の高い部門の値よりも多くのサラリーをもらっている従業員の名前を知りたい」
という場合、（いろいろなアプローチが考えられますが）、その一つの例として以下のように書くことができます。
まず、各部門における平均サラリーを見てみます。
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
WORKDEPT 2
-------- ---------------------------------
B01 94250.000000000000000000000000
E01 80175.000000000000000000000000
C01 77222.500000000000000000000000
A00 70850.000000000000000000000000
D11 58783.636363636363636363636363
D21 51240.000000000000000000000000
E21 47086.666666666666666666666666
E11 45305.714285714285714285714285
8 レコードが選択されました。
「３番目にサラリー平均が高い部門の値」が欲しいので、FETCH FIRSTとOFFSETを使います。
つまり、２行スキップして３番目の値を取得します。
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
OFFSET 2 ROWS FETCH FIRST 1 ROWS ONLY
WORKDEPT 2
-------- ---------------------------------
C01 77222.500000000000000000000000
1 レコードが選択されました。
これを副選択照会(subselect)に使って、３番目にサラリー平均の高い部門の値よりも多くのサラリーをもらっている従業員の名前、をリストします。
SELECT LASTNAME, SALARY FROM EMPLOYEE
WHERE
SALARY > (
SELECT AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
OFFSET 2 ROWS FETCH FIRST 1 ROW ONLY
)
ORDER BY SALARY
LASTNAME SALARY
--------------- -----------
GEYER 80175.00
SPENSER 86150.00
HENDERSON 89750.00
THOMPSON 94250.00
PULASKI 96170.00
KWAN 98250.00
HAAS 152750.00
7 レコードが選択されました。
LIMIT ... OFFSETの代替構文を使ってみる
Db2 11.1では、PostgreSQLやMySQLなど他のデータベース製品で使用されるSQLも簡単にDb2環境で実行できるようにする機能拡張が含まれており、FETCH FIRST ... OFFSET節の代替として、シンプルにLIMIT/OFFSETを指定することもできるようになりました。
| 代替構文 | 同等の構文 |
|---|---|
| LIMIT x | FETCH FIRST x ROWS ONLY |
| LIMIT x OFFSET y | OFFSET y ROWS FETCH FIRST x ROWS ONLY |
| LIMIT y,x | OFFSET y ROWS FETCH FIRST x ROWS ONLY |
こちらを使って同じ結果を取得してみます。
SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
LIMIT 1 OFFSET 2
WORKDEPT 2
-------- ---------------------------------
C01 77222.500000000000000000000000
1 レコードが選択されました。
それでは、３番目にサラリー平均の高い部門の値よりも多くのサラリーをもらっている従業員の名前をリストします。
"LIMIT x OFFSET y"は、"LIMIT x,y"ではなく、"LIMIT y,x"であることに注意（！）
SELECT LASTNAME, SALARY FROM EMPLOYEE
WHERE
SALARY > (
SELECT AVG(SALARY) FROM EMPLOYEE
GROUP BY WORKDEPT
ORDER BY AVG(SALARY) DESC
LIMIT 2,1
)
ORDER BY SALARY
LASTNAME SALARY
--------------- -----------
GEYER 80175.00
SPENSER 86150.00
HENDERSON 89750.00
THOMPSON 94250.00
PULASKI 96170.00
KWAN 98250.00
HAAS 152750.00
7 レコードが選択されました。
少しシンプルになりました。
参考資料
-
-
- FETCH FIRST 節の OFFSET オプション
- 代替構文