CTE（Common Table Expression）でスマートに実装するSQL Serverのページング

SQLでページング機能（スキップ/テイク）を実装する方法

SQL Serverでページング機能を実装するには、OFFSETとFETCH NEXTクエリ句を使用します。これらの句を使用することで、クエリ結果の一部のみを返すことができます。これは、大量のデータセットを扱う場合に特に役立ちます。

例

以下の例では、Customersテーブルから顧客のリストを取得し、1ページあたり10件ずつ表示する方法を示します。

SELECT
  *
FROM
  Customers
ORDER BY
  CustomerID
OFFSET
  10 * (page - 1)
ROWS
FETCH NEXT
  10 ROWS ONLY;

解説

• OFFSET句は、クエリ結果からスキップする行数を指定します。上記の例では、10 * (page - 1)という式を使用しています。これは、pageが1の場合、最初の10行をスキップし、pageが2の場合、最初の20行をスキップすることを意味します。
• FETCH NEXT句は、クエリ結果から返す行数を指定します。上記の例では、10 ROWS ONLYという式を使用しています。これは、10行のみを返すことを意味します。

JOIN操作とページング

JOIN操作を使用している場合でも、ページング機能を実装できます。ただし、JOIN条件をWHERE句ではなくON句に指定する必要があることに注意してください。

以下の例では、OrdersテーブルとCustomersテーブルをCustomerID列で結合し、顧客の注文履歴をページングする方法を示します。

SELECT
  o.*,
  c.CustomerName
FROM
  Orders o
JOIN
  Customers c
ON
  o.CustomerID = c.CustomerID
ORDER BY
  o.OrderID
OFFSET
  10 * (page - 1)
ROWS
FETCH NEXT
  10 ROWS ONLY;

• ページング機能を実装する際には、ORDER BY句を使用することが重要です。これにより、クエリ結果を順序付けして、どの行がスキップされるのかを明確にすることができます。
• クエリのパフォーマンスを向上させるために、インデックスを使用することができます。
• ページング機能を実装する際には、クライアントアプリケーションでページングロジックを実装する必要があります。これにより、ユーザーがページを切り替えることができるようになります。

CREATE TABLE Customers (
  CustomerID int PRIMARY KEY,
  CustomerName varchar(50) NOT NULL,
  Email varchar(100) UNIQUE NOT NULL,
  Country varchar(50) NOT NULL
);

データ挿入

INSERT INTO Customers (CustomerID, CustomerName, Email, Country)
VALUES
  (1, 'John Doe', '[email protected]', 'USA'),
  (2, 'Jane Doe', '[email protected]', 'USA'),
  (3, 'Peter Jones', '[email protected]', 'UK'),
  (4, 'Mary Smith', '[email protected]', 'UK'),
  (5, 'David Williams', '[email protected]', 'Canada');

ページング機能を実装するクエリ

SELECT
  *
FROM
  Customers
ORDER BY
  CustomerID
OFFSET
  10 * (page - 1)
ROWS
FETCH NEXT
  10 ROWS ONLY;

クエリの実行

-- page = 1の場合
SELECT
  *
FROM
  Customers
ORDER BY
  CustomerID
OFFSET
  0
ROWS
FETCH NEXT
  10 ROWS ONLY;

-- page = 2の場合
SELECT
  *
FROM
  Customers
ORDER BY
  CustomerID
OFFSET
  10
ROWS
FETCH NEXT
  10 ROWS ONLY;

出力

CustomerID | CustomerName | Email                | Country
-----------+--------------+----------------------+---------
1           | John Doe     | [email protected] | USA
2           | Jane Doe     | [email protected] | USA
3           | Peter Jones   | [email protected] | UK
4           | Mary Smith   | [email protected] | UK
5           | David Williams | [email protected] | Canada

11          | NULL          | NULL                  | NULL
12          | NULL          | NULL                  | NULL
13          | NULL          | NULL                  | NULL
14          | NULL          | NULL                  | NULL
15          | NULL          | NULL                  | NULL

説明

• 上記のクエリは、Customersテーブルから顧客のリストを取得し、CustomerID列で昇順に並べ替えます。

ROW_NUMBER()関数を使用して、各行に固有の行番号を割り当てることができます。その後、WHERE句を使用して、必要な行のみを抽出することができます。

SELECT
  *
FROM
  Customers
WHERE
  ROW_NUMBER() OVER (ORDER BY CustomerID) BETWEEN 10 * (page - 1) + 1 AND 10 * page;

サブクエリを使用する

サブクエリを使用して、必要な行のIDのリストを取得することができます。その後、IN句を使用して、メインクエリでこれらのIDをフィルタリングすることができます。

SELECT
  *
FROM
  Customers
WHERE
  CustomerID IN (
    SELECT
      CustomerID
    FROM
      (
        SELECT
          ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber,
          CustomerID
        FROM
          Customers
      ) AS subquery
    WHERE
      RowNumber BETWEEN 10 * (page - 1) + 1 AND 10 * page
  );

CTEを使用する

CTE (Common Table Expression)を使用して、必要な行のセットを定義することができます。その後、このCTEをメインクエリで使用することができます。

WITH Customers AS (
  SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNumber
  FROM
    Customers
)
SELECT
  *
FROM
  Customers
WHERE
  RowNumber BETWEEN 10 * (page - 1) + 1 AND 10 * page;

各方法の比較