【MySQL】CONCATとGROUP_CONCATを駆使して、複数レコードの文字列を結合してみる。
2019/03/06
MySQLで複数行の文字列レコードをひと纏めにして、１レコードとして出力する方法を備忘録としてポストします。
以下の旅行テーブルのレコードを利用します。
CONCAT()
MysqlのCONCAT()関数は文字列の結合を行う関数。
使い方は以下のように。
CONCAT() 書式
|
1 |
CONCAT(文字列1, 文字列2, ・・・文字列N) |
CONCAT() 実例
全レコードを半角カンマ区切りで取得
|
1 2 3 4 |
SELECT CONCAT(id, ',', CONVERT(`name_first` USING cp932), ',', hotel_id, ',', room_number) AS Record FROM travels |
結果
↑ CSV出力などで扱い易い、半角カンマ区切りでレコードを作ってみました。
CONCAT() 実例2
CONCAT()の引数に NULL が含まれる場合、結果がNULLとなってしまいます。
以下のレコードで試します。
CONCAT() NULLを含む場合の実例
全レコードを半角カンマ区切りで取得
|
1 2 3 4 |
SELECT CONCAT(id, ',', CONVERT(`name_family` USING cp932), ',', CONVERT(`name_first` USING cp932), ',', hotel_id, ',', room_number) AS Record FROM travels |
結果
↑ 4,7の結果がNULLで返るために、空白となっています。
GROUP_CONCAT()
GROUP_CONCAT()関数は複数レコードを1行に纏めることが出来ます。
セパレータを指定することも可能です。
GROUP_CONCAT() 書式
|
1 |
GROUP_CONCAT(カラム名 SEPARATOR ',' ) |
GROUP_CONCAT() 実例
ホテル別に「姓名」を１カラムに結合して出力する実例
|
1 2 3 4 5 6 7 |
SELECT hotel_id, GROUP_CONCAT(name_family, name_first SEPARATOR ', ') AS name FROM travels GROUP BY hotel_id |
↑ hotel_id へ GROUP BY を効かせ、データを hotel_id ごとにするのも忘れずに。
結果
GROUP_CONCAT() NULLを含む場合の実例
NULLを含む場合の実例。
以下レコードを使用します。
ホテル別に「姓名」を１カラムに結合して出力する実例
|
1 2 3 4 5 6 |
SELECT hotel_id, GROUP_CONCAT(name_family, name_first SEPARATOR ',') AS name FROM travels GROUP BY hotel_id |
結果
↑ 4,7のレコードが抜けています。
本題
CONCAT()とGROUP_CONCAT()を駆使してみる
本題 実例1
ホテル別に「名」を出力、名がNULLの場合は「名無し」を代替出力
|
1 2 3 4 5 6 7 8 9 |
SELECT hotel_id, CONCAT( IFNULL(GROUP_CONCAT(name_family SEPARATOR ', '), '名無し'), ' ' ) AS name FROM travels GROUP BY hotel_id |
本題 実例2 実例1の書き方を変えてみる
結果は同じですが、CONCATとGROUP_CONCATの順番を入れ替えても書けます。
|
1 2 3 4 5 6 7 8 9 |
SELECT hotel_id, GROUP_CONCAT( CONCAT(IFNULL(name_family, '名無し')) SEPARATOR ', ' ) AS name FROM travels GROUP BY hotel_id |
本題 実例3
結果が NULL のままでは分かりにくいので、姓が抜けている場合は「姓」、名が抜けている場合は「名」と代替表示させてみます。
「姓」がNULLの場合は「姓」、「名」がNULLの場合は「名」を代替出力
|
1 2 3 4 5 6 7 8 9 |
SELECT hotel_id, GROUP_CONCAT( CONCAT(IFNULL(name_family, '姓')),CONCAT(IFNULL(name_first, '名')) SEPARATOR ', ' ) AS name FROM travels GROUP BY hotel_id |
結果
NULLのレコードも代替テキストで表示される、欲しい形となりました。
SQL、やはり奥深いけど便利ですね。
やっぱりCakePHPなどフレームワークを使っていても、SQLは書くべきかも知れませんね。
