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