DB

DB上でソートして上位n件のデータを取得してみた

 

前回、Oracle上でソートして上位n件のデータを取得するSQLを作成したが、今回は他のDB上で同様のSQLを作成してみたので、共有する。

前提条件

下記記事に記載されている、MySQLとPostgreSQLでのユーザー作成とUSER_DATAテーブル作成が完了していること。

MySQLでユーザーを作成しテーブルを追加してみたMySQLの場合は、Workbenchを利用すると、スキーマ(データベース)やユーザーの作成をGUIベースで実施できる。今回は、Work...
Postgresqlでユーザーを作成しテーブルを追加してみたPostgresqlの場合は、pgAdminを利用すると、ユーザーやデータベースの作成をGUIベースで実施できる。今回は、pgAdmin...

また、下記記事の「前提条件」の内容が完了していること。

Spring BootでSQL Serverに接続しMyBatisを利用してみた今回は、Spring Bootアプリケーションで接続するデータベースをSQL Serverに変更してみたので、そのサンプルプログラムを共...

さらに、各DB上のUSER_DATAテーブルに、以下のデータが追加されていること。（下記はOracleの例）

SELECT * FROM USER_DATA
前提条件_2

やってみたこと

  1. Oracleの場合の実行結果
  2. MySQLの場合の実行結果
  3. PostgreSQLの場合の実行結果
  4. SQL Serverの場合の実行結果

Oracleの場合の実行結果

Oracleの場合の実行結果は、下記記事を参照のこと。

Oracle上でソートして上位n件のデータを取得してみたデータベース上のデータを抽出する際、ある一定の規則でソートして上位n件のデータを取得したい、という場合がある。今回は、Oracle上で、...



MySQLの場合の実行結果

MySQLで、ROW_NUMBER分析関数を利用し、USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。ROW_NUMBER分析関数の記載方法は、Oracleの場合と同じになる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        STR_TO_DATE(
            CONCAT(LPAD(BIRTH_YEAR, 4, '0')
                 , LPAD(BIRTH_MONTH, 2, '0')
                 , LPAD(BIRTH_DAY, 2, '0')) 
            , '%Y%m%d')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5
MySQL実行結果_RN利用

また、先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用しないで実行した結果は、以下の通り。ソート処理は副問い合わせの外で行い、LIMIT句で上位5件の取得を行っている。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        STR_TO_DATE(
            CONCAT(LPAD(BIRTH_YEAR, 4, '0')
                 , LPAD(BIRTH_MONTH, 2, '0')
                 , LPAD(BIRTH_DAY, 2, '0')) 
           , '%Y%m%d')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO
    FROM USER_DATA
) usr
ORDER BY usr.ID
LIMIT 5
MySQL実行結果_RN未利用



PostgreSQLの場合の実行結果

PostgreSQLで、ROW_NUMBER分析関数を利用し、USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。ROW_NUMBER分析関数の記載方法は、Oracleの場合と同じになる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
       TO_DATE(
             CONCAT(TO_CHAR(BIRTH_YEAR, '0000')
                  , TO_CHAR(BIRTH_MONTH, '00')
                  , TO_CHAR(BIRTH_DAY, '00'))
            , 'YYYYMMDD')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5
PostgreSQL実行結果_RN利用

また、先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用しないで実行した結果は、以下の通り。MySQLの場合と同様に、ソート処理は副問い合わせの外で行い、LIMIT句で上位5件の取得を行っている。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
       TO_DATE(
             CONCAT(TO_CHAR(BIRTH_YEAR, '0000')
                  , TO_CHAR(BIRTH_MONTH, '00')
                  , TO_CHAR(BIRTH_DAY, '00'))
            , 'YYYYMMDD')
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO
    FROM USER_DATA
) usr
ORDER BY usr.ID ASC
LIMIT 5
PostgreSQL実行結果_RN未利用



SQL Serverの場合の実行結果

SQL Serverで、ROW_NUMBER分析関数を利用し、USER_DATAテーブルのデータを、生年月日と性別を加工し、IDの昇順にソートした後で、上位5件のみを取得するSQLの実行結果は、以下の通り。ROW_NUMBER分析関数の記載方法は、Oracleの場合と同じになる。

SELECT usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        CONVERT(DATETIME
            , (FORMAT(BIRTH_YEAR,'0000') 
             + FORMAT(BIRTH_MONTH,'00') 
             + FORMAT(BIRTH_DAY,'00')))
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO,
        ROW_NUMBER() OVER (ORDER BY ID ASC) RN
    FROM USER_DATA
) usr
WHERE usr.RN <= 5
SQLServer実行結果_RN利用

また、先ほどのSQLと同様のことを、ROW_NUMBER 分析関数を利用しないで実行した結果は、以下の通り。ソート処理は副問い合わせの外で行い、先頭のSELECTに付与しているTOP句で上位5件の取得を行っている。

SELECT TOP 5 usr.* 
FROM(
    SELECT 
        ID, 
        NAME, 
        CONVERT(DATETIME
            , (FORMAT(BIRTH_YEAR,'0000') 
             + FORMAT(BIRTH_MONTH,'00') 
             + FORMAT(BIRTH_DAY,'00')))
        AS BIRTHDAY,
        CASE SEX 
            WHEN '1' THEN '男'
            WHEN '2' THEN '女'
            ELSE ''
        END AS SEX_STR,
        MEMO
    FROM USER_DATA
) usr
ORDER BY usr.ID ASC
SQLServer実行結果_RN未利用

要点まとめ

  • 各DB上でソートして上位n件のデータを取得するには、Oracleの場合と同様にROW_NUMBER 分析関数を利用するか、SQLの副問い合わせ外でソートした後で、LIMIT句やTOP句で上位n件を取得すればよい。
RELATED POST