MySQL、2テーブルより値を結合しソートするテクニック(oreder by、union)


本日はMySQLで2テーブルの、フィールド名が違う2フィールドを結合し、ソートするテクニックをご紹介します。
複数SELECT文の結合はUNIONを使いますが、ネットを検索しても、なかなか異なるフィールド名を結合してからのソートが掲載されてなかったので、ブログに書きました。

WebエンジニアとしてMySQLのコードを書くのは最終的な手段として取っておきたい所ですが、結局複雑なことをやろうとするとSQLを書いた方が楽ですし、レスポンス的にも早いですよね。ぜひ覚えておきたい技です。(いつかは役に立つと思います。)

 

今回は、CPIのACE01サーバーを利用いたしました。

 

 

最終的なSQL

 

表題のSQLは下記で実現可能です。このSQLを見て理解できるかたは、「いいね」ボタンを押してから、そっと画面を閉じてください。
分からない方は、下記で動画か、活字で説明いたします。

SELECT u.id,u.username,MAX(u.value)
FROM
(
SELECT user.id,username,math_value value FROM user
INNER JOIN mathscore ON user.id = mathscore.id
UNION
SELECT user.id,username,en_value value FROM user
INNER JOIN englishscore ON user.id = englishscore.id
) u
GROUP BY u.id ORDER BY MAX(u.value) DESC;

 

テーブル定義

 

テーブルはユーザーテーブルと、ユーザーのテストの点数が入っているテーブルを用意しました。
数学の点数(mathscore)と、英語(englishscore)の点数は、テストを受けていないユーザーがいると仮定し、レコードがあったり、なかったりしています。

テーブル名:user

 

テーブル名:score
テストの成績などが入っているテーブルです。

 

scoreと同じ構成のテーブルでmathscoreと、englishscoreを定義しています。

 

 
 

データ取得(INNER JOIN:内部結合)

 

では実際にデータを取得していきます。
まずは、userテーブルのidと、scoreテーブルのidを結合条件として取得します。

SELECT username,value FROM user
INNER JOIN score ON user.id = score.id;

userテーブルのidに一致したレコードだけ結合されました。

+----------+-------+

| username | value |

+----------+-------+

| abe      |    80 |

| sato     |    65 |

| suzuki   |    90 |

| tanaka   |    70 |

| ito      |    40 |

+----------+-------+

 

次に、mathscoreと、englishscoreテーブルも結合します。

SELECT username,math_value FROM user
INNER JOIN mathscore ON user.id = mathscore.id;

+----------+------------+

| username | math_value |

+----------+------------+

| abe      |         90 |

| sato     |         70 |

| suzuki   |         50 |

+----------+------------+

 

SELECT username,en_value FROM user
INNER JOIN englishscore ON user.id = englishscore.id;

+----------+----------+

| username | en_value |

+----------+----------+

| suzuki   |       95 |

| tanaka   |       60 |

| ito      |      100 |

+----------+----------+

一致したidのレコードだけ抽出されたのが分かるかと思います。
idが一致しない場合にもuserテーブルを元に一覧を作成したい場合は、LEFT JOINなどを使います。(今回は割愛)

 

複数SELECTをまとめる(UNION)

 

それでは上記で紹介した複数のSELECT文をまとめて表示したいと思います。まとめて表示するのはUNION句を使います。

SELECT username,math_value FROM user
INNER JOIN mathscore ON user.id = mathscore.id
UNION
SELECT username,en_value FROM user
INNER JOIN englishscore ON user.id = englishscore.id;

+----------+------------+

| username | math_value |

+----------+------------+

| abe      |         90 |

| sato     |         70 |

| suzuki   |         50 |

| suzuki   |         95 |

| tanaka   |         60 |

| ito      |        100 |

+----------+------------+

上記で作成したSELECT文をUNIONでつないだだけです。

 

では次に、UNIONで複数のSELECT文をまとめましたが、テスト点数順にソート(order)します。

SELECT u.id,u.username,u.value
FROM
(
SELECT user.id,username,math_value value FROM user
INNER JOIN mathscore ON user.id = mathscore.id
UNION
SELECT user.id,username,en_value value FROM user
INNER JOIN englishscore ON user.id = englishscore.id
) u
ORDER BY u.value DESC;

+----+----------+-------+

| id | username | value |

+----+----------+-------+

|  5 | ito      |   100 |

|  3 | suzuki   |    95 |

|  1 | abe      |    90 |

|  2 | sato     |    70 |

|  4 | tanaka   |    60 |

|  3 | suzuki   |    50 |

+----+----------+-------+

SELECT文のFROMを「 ( ) u 」 でくくり、さきほどの「SELECT文 UNION SELECT文」を挿入し、ORDER BY で、u.valueを指定しています。
点数の結果フィールドは、フィールド名が違うので、「math_value value」、「en_value value」とし、u.valueで指定できるようにしています。

 

さて、一見良さそうに見える上記のSQLですが、実はsuzukiさんが重複して抽出されています。
このsuzukiさんの重複を解除しつつ、より点数の高い成績で並び替えを行います。

SELECT u.id,username,MAX(u.value)
FROM
(
SELECT user.id,username,math_value value FROM user
INNER JOIN mathscore ON user.id = mathscore.id
UNION
SELECT user.id,username,en_value value FROM user
INNER JOIN englishscore ON user.id = englishscore.id
) u
GROUP BY u.id ORDER BY MAX(u.value) DESC;

+----+----------+--------------+

| id | username | MAX(u.value) |

+----+----------+--------------+

|  5 | ito      |          100 |

|  3 | suzuki   |           95 |

|  1 | abe      |           90 |

|  2 | sato     |           70 |

|  4 | tanaka   |           60 |

+----+----------+--------------+

GROUP BYで、u.idをまとめ、MAX( u.value )で、点数の高い方を取得しています。

 

以上で「2テーブルの2フィールドの違うフィールド名より値を結合しソートするテクニック」を終わります。Webのエンジニアとして、なかなか使う場面も少ないでしょうが、いざと言う時はSQL書くことも多々ありますので、これくらいのSQLは覚えておいても良いのではないでしょうか。

 

関連タグ: 

この記事をシェアする:

Author
この記事を書いた人:阿部 正幸

KDDIウェブコミュニケーションズ
クラウドホスティング事業本部 エバンジェリスト

CPIスタッフブログ編集長。ACE01,SmartReleaseをリリース後、現職の「エバンジェリスト」として、web制作に関する様々なイベントに登場

Line@登録よろしくお願いします

Web制作に関する情報や、CPIノベルティのプレゼント、サーバーの無償提供などを定期的に発信しています。
ぜひ、登録ください。