MySQLで末尾の文字列に基づいてレコードをソートする方法

MySQLで文字列の末尾部分（例：「-D」や「-Smith」など）に基づいてレコードをソートしたい場合は、CASE式とORDER BY句を組み合わせることで実現できます。ここでは、実際のサンプルコードを使いながら、手順をわかりやすく解説します。

1. サンプルテーブルの作成

まずはデモ用のテーブルを作成します。顧客ID（ClientId）を自動採番の主キーとし、顧客名（ClientName）を格納するシンプルな構造です。

mysql> create table DemoTable
    -> (
    -> ClientId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ClientName varchar(20)
    -> );
Query OK, 0 rows affected (0.54 sec)

2. テストデータの挿入

INSERTコマンドでいくつかのレコードを追加します。一部のレコードには「John-D」や「Mike-Smith」のように、ハイフンと末尾文字列が含まれている点がポイントです。

mysql> insert into DemoTable(ClientName) values('Mike');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable(ClientName) values('John');
Query OK, 1 row affected (0.22 sec)
mysql> insert into DemoTable(ClientName) values('John-D');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable(ClientName) values('John-Smith');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable(ClientName) values('Mike-Smith');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable(ClientName) values('Mike-D');
Query OK, 1 row affected (0.15 sec)

3. 登録されたレコードの確認

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行すると、次のような結果が得られます。

+----------+------------+
| ClientId | ClientName |
+----------+------------+
|        1 | Mike       |
|        2 | John       |
|        3 | John-D     |
|        4 | John-Smith |
|        5 | Mike-Smith |
|        6 | Mike-D     |
+----------+------------+
6 rows in set (0.00 sec)

4. 末尾の文字列でソートするクエリ

ここが本題です。以下のクエリでは、CASE式を使ってハイフン（-）以降の文字列を抽出し、その値（last）と顧客名（ClientName）の2段階でORDER BYによるソートを行っています。ハイフンを含まないレコードは空文字として扱われるため、結果の先頭に配置されます。

mysql> select
    -> case
    -> when right(ClientName,length(ClientName)-instr(ClientName,'-')) = `ClientName` THEN ''
    -> else right(`ClientName`,length(`ClientName`)-INSTR(`ClientName`,'-'))
    -> end as `last`,
    -> `ClientName`
    -> from
    -> DemoTable
    -> ORDER BY
    -> `last`,`ClientName`;

5. 実行結果

クエリを実行すると、次のような出力になります。

+-------+------------+
| last  | ClientName |
+-------+------------+
|       | John       |
|       | Mike       |
| D     | John-D     |
| D     | Mike-D     |
| Smith | John-Smith |
| Smith | Mike-Smith |
+-------+------------+
6 rows in set (0.00 sec)

結果のポイント

結果を見ると、以下の順序でソートされていることが確認できます。

• ハイフンを含まないレコード（John、Mike）：
  lastが空文字になるため最初に表示され、ClientNameのアルファベット順に並びます。
• 「D」で終わるレコード（John-D、Mike-D）：
  lastが「D」のグループとしてまとめられます。
• 「Smith」で終わるレコード（John-Smith、Mike-Smith）：
  lastが「Smith」のグループとして最後にまとめられます。

このようにCASE式とRIGHT関数・INSTR関数を組み合わせることで、文字列の一部分に基づいた柔軟なソートが可能になります。同様の手法は、ファイル名の拡張子や接尾辞ごとにデータを整理したい場合など、さまざまな場面で応用できます。