[MySQL]GROUP_CONCATで縦のデータをカンマ区切りで取得

複数レコードのデータを連結して取得したい

MySQL(MariaDB)で、GROUP_CONCAT を使って、縦に並んだ複数レコードのデータを1列(カンマ区切り)で取得するSQLを書く方法をまとめます。

SQL Serverでは、FOR XML PATH('') を使用して取得できます。

[SQL Server] 縦に並んだデータを横にカンマ区切りの列データで取得する方法

上記記事では SQL Server の例をまとめていますが今回は MySQL で似たようなことをやってみます。

GROUP_CONCAT

MySQL(MariaDB)では GROUP BY でグループ化(Group By)した上で、GROUP_CONCAT を使用することで実現します。

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数

GROUP_CONCAT は集約関数の一つで、SUM() や MAX() と同じく、値のセットに対して処理を行います。Group By によって集約されたグループから、文字列として連結した値を取得します。

サンプル

CREATE TABLE sales
    (`id` int, `item` varchar(30), `date` datetime)
;

INSERT INTO sales
    (`id`, `item`, `date`)
VALUES
    (1, '商品C', '2017-04-01 00:00:00'),
    (2, '商品B', '2017-04-01 00:00:00'),
    (3, '商品A', '2017-04-01 00:00:00'),
    (4, '商品A', '2017-04-02 00:00:00'),
    (5, '商品B', '2017-04-02 00:00:00'),
    (6, '商品C', '2017-04-03 00:00:00'),
    (7, '商品A', '2017-04-03 00:00:00'),
    (8, '商品D', '2017-04-04 00:00:00'),
    (9, '商品C', '2017-04-05 00:00:00'),
    (10, '商品C', '2017-04-05 00:00:00')
;

例えば上記のようなデータで考えます。商品の売り上げデータを保持しているテーブルです。フィールドは以下の3つです。

• id: 主キー
• item: 商品名
• date: 売上日

ここで売上日ごとにデータを集約し、商品名を連結したデータを取得したいという時に、GROUP_CONCAT が使えます。例えばそれぞれの商品の直近の売上日を知りたければ Group By + Max() で取得できるように、集約したデータをに対して連結した文字列を GROUP_CONCAT で取得できるということです。

SELECT
    GROUP_CONCAT(item) AS 売れた商品,
    date AS 売上日
FROM sales
GROUP BY date

GROUP_CONCATの構文

GROUP_CONCAT([DISTINCT] expr [,expr …]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name …]]
[SEPARATOR str_val])

GROUP_CONCAT の基本は「連結する列を指定してカンマ区切りで取得」ですが、それ以外にもパラメータで細かく指定することが可能です。

例えば、デフォルトの連結用の文字列(SEPARATOR)はカンマ(,)ですが、これを変更することが可能です。連結する順番も ORDER BY で指定できます。DISTINCT で重複を省くこともできます。

構文は上述の通りです。。ここでは、連結文字と並び順と重複の除去を設定する例を以下に示します。

SELECT
    GROUP_CONCAT(DISTINCT item ORDER BY item ASC SEPARATOR '/') AS 売れた商品,
    date AS 売上日
FROM sales
GROUP BY date

• 最終行のデータから「商品C」の重複が除去されている。
• 区切り文字が指定したスラッシュ(/)になっている。
• 列挙された商品名の並び順が商品名昇順になっている。

3つのパラメータを設定したため、以上3点に違いがあります。

以上、使いこなせると便利な GROUP_CONCAT のまとめでした。

参考URL

• MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数