OFFSET FETCH は SQL Server 2012 から追加されました。
ORDER BY 句 (Transact-SQL)
クエリの結果を返す行数を制限するために使用します。主にページング処理とかで使われると思います。
2008 R2 まではページング処理を行おうとすると、サブクエリを使う必要がありました。
こんなデータがあった時に、
-- 準備 CREATE TABLE [受注] ( [Id] [int] identity(1,1) not null primary key, [受注番号] [nvarchar](10) not null, [受注日] [date] not null, [金額] [int] not null ) go insert into [受注] values ('J01', '2013/02/02', 1200), ('J02', '2013/02/05', 2400), ('J03', '2013/02/04', 3600), ('J04', '2013/02/02', 4800), ('J05', '2013/02/09', 6000), ('J06', '2013/02/07', 7200), ('J07', '2013/02/08', 8400), ('J08', '2013/02/06', 9600), ('J09', '2013/02/02', 10800) go -- データ確認 select * from [受注] order by [受注日]
4行目から3件取得したい場合は、こんなクエリになります
-- 昔のページング declare @start int = 4 declare @count int = 3 select [Id], [受注番号], [受注日], [金額] from (select ROW_NUMBER() over(order by [受注日] ) as [rownum], * from [受注] ) as [subquery] where [rownum] between @start and @start + @count - 1
2012 から導入された OFFSET FETCH を使うと…
-- 新しいページング select * from [受注] order by [受注日] offset @start - 1 rows fetch next @count rows only
シンプルに書けますね!
offset の値は、0 が先頭行を表します。また、offset や fetch に渡す値は、数値の定数(リテラル)や単一の値を返すサブクエリでも代用出来ます。
例えば、1ページの表示件数をテーブルに保持しているケースでは*1
-- offset fetch の指定にサブクエリも使える select * from [受注] order by [受注日] offset @start - 1 rows fetch next ( select [件数] from (values (5)) as [設定テーブル]([件数]) ) rows only
これからのページング処理は、OFFSET - FETCH に決まりですね!
クエリ全文
-- 準備 CREATE TABLE [受注] ( [Id] [int] identity(1,1) not null primary key, [受注番号] [nvarchar](10) not null, [受注日] [date] not null, [金額] [int] not null ) go insert into [受注] values ('J01', '2013/02/02', 1200), ('J02', '2013/02/05', 2400), ('J03', '2013/02/04', 3600), ('J04', '2013/02/02', 4800), ('J05', '2013/02/09', 6000), ('J06', '2013/02/07', 7200), ('J07', '2013/02/08', 8400), ('J08', '2013/02/06', 9600), ('J09', '2013/02/02', 10800) go -- データ確認 select * from [受注] order by [受注日] -- 昔のページング declare @start int = 4 declare @count int = 3 select [Id], [受注番号], [受注日], [金額] from (select ROW_NUMBER() over(order by [受注日] ) as [rownum], * from [受注] ) as [subquery] where [rownum] between @start and @start + @count - 1 -- 新しいページング select * from [受注] order by [受注日] offset @start - 1 rows fetch next @count rows only -- offset fetch の指定にサブクエリも使える select * from [受注] order by [受注日] offset @start - 1 rows fetch next ( select [件数] from (values (5)) as [設定テーブル]([件数]) ) rows only go -- 後片付け drop table [受注]