本日はMySQLで2テーブルの、フィールド名が違う2フィールドを結合し、ソートするテクニックをご紹介します。
複数SELECT文の結合はUNIONを使いますが、ネットを検索しても、なかなか異なるフィールド名を結合してからのソートが掲載されてなかったので、ブログに書きました。
WebエンジニアとしてMySQLのコードを書くのは最終的な手段として取っておきたい所ですが、結局複雑なことをやろうとするとSQLを書いた方が楽ですし、レスポンス的にも早いですよね。ぜひ覚えておきたい技です。（いつかは役に立つと思います。）
今回は、CPIのACE01サーバーを利用いたしました。
「Webサイトからお問い合わせが来ない…」とお悩みの方必見！
当サイトのノウハウを詰め込んだ『Web集客の無料ガイド』をご提供
最終的な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は覚えておいても良いのではないでしょうか。
