MySQLとMariaDBでUNIONクエリとGROUP_CONCAT関数を使いこなす：詳細解説とサンプルコード

2024-06-15

MySQLとMariaDBにおけるUNIONクエリでのGROUP_CONCAT関数使用

MySQLとMariaDBでは、GROUP_CONCAT関数を使用して、UNIONクエリ内の結果セットを連結することができます。これは、複数の行のデータを1つの列にまとめる場合に役立ちます。

構文

SELECT GROUP_CONCAT(DISTINCT 列名 SEPARATOR 区切り文字)
FROM テーブル名
[WHERE 条件式]
[GROUP BY 列名]
[ORDER BY 列名]
[LIMIT 件数];

説明

GROUP_CONCAT：複数の値を連結する関数です。
DISTINCT：連結される値の重複を排除します。
SEPARATOR：連結される値の間に入れる区切り文字を指定します。デフォルトはカンマ（,）です。
列名：連結する列を指定します。
テーブル名：対象となるテーブルを指定します。
WHERE 条件式：連結する行を絞り込む条件を指定します。
GROUP BY 列名：結果セットをグループ化する列を指定します。
ORDER BY 列名：連結される値の順序を指定します。
LIMIT 件数：返す行数を制限します。

UNIONクエリとの組み合わせ

UNIONクエリとGROUP_CONCAT関数を組み合わせることで、複数のクエリ結果を連結することができます。

SELECT GROUP_CONCAT(DISTINCT 列名 SEPARATOR 区切り文字)
FROM (
  クエリ1
  UNION
  クエリ2
) AS 結果セット
[GROUP BY 列名]
[ORDER BY 列名]
[LIMIT 件数];

例

次の例では、customers テーブルと orders テーブルから顧客の名前と注文内容を連結し、1つの列にまとめた結果セットを出力します。

SELECT GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ') AS customer_orders
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY c.name;

補足

MySQL 8.0以降では、GROUP_CONCAT関数の代わりにCONCAT_WS関数を使用することもできます。CONCAT_WS関数は、区切り文字を指定する引数を持つため、より柔軟な連結が可能です。
MariaDB 10.2以降では、GROUP_CONCAT関数の結果をJSON形式で取得することもできます。

注意事項

上記の情報は、MySQL 8.0とMariaDB 10.2を対象としています。古いバージョンでは、機能や構文が異なる場合があります。
複雑なクエリを作成する場合は、パフォーマンスと結果の正確性を考慮する必要があります。

MySQLとMariaDBにおけるUNIONクエリでのGROUP_CONCAT関数使用：サンプルコード

テーブル構造

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

データ

INSERT INTO customers (name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

INSERT INTO orders (customer_id, product_name) VALUES
  (1, 'Book'),
  (1, 'Laptop'),
  (2, 'Mouse'),
  (3, 'Keyboard'),
  (3, 'Monitor');

クエリ

SELECT GROUP_CONCAT(DISTINCT c.name SEPARATOR ', ') AS customer_orders, o.product_name
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY o.id
ORDER BY c.name;

出力

customer_orders, product_name
Alice, Book, Laptop
Bob, Mouse
Charlie, Keyboard, Monitor

例2：部門ごとの従業員の名前と給与を連結

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  department_id INT NOT NULL,
  salary DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

INSERT INTO departments (name) VALUES
  ('Sales'),
  ('Marketing'),
  ('Development');

INSERT INTO employees (name, department_id, salary) VALUES
  ('John Doe', 1, 50000.00),
  ('Jane Doe', 1, 45000.00),
  ('Peter Jones', 2, 40000.00),
  ('Mary Smith', 2, 35000.00),
  ('David Williams', 3, 60000.00),
  ('Susan Miller', 3, 55000.00);

SELECT d.name AS department, GROUP_CONCAT(DISTINCT e.name SEPARATOR ', ') AS employee_names, GROUP_CONCAT(DISTINCT e.salary SEPARATOR ', ') AS salaries
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.id
GROUP BY d.id
ORDER BY d.name;

department, employee_names, salaries
Sales, John Doe, Jane Doe, 50000.00, 45000.00
Marketing, Peter Jones, Mary Smith, 40000.00, 35000.00
Development, David Williams, Susan Miller, 60000.00, 55000.00

これらの例は、GROUP_CONCAT関数とUNIONクエリを組み合わせることで、複雑なデータ分析を行うための強力なツールを提供することを示しています。

上記の例は基本的な使用方法を示しています。実際の使用状況では、必要に応じてWHERE条件やORDER BY句を追加することができます。
GROUP_CONCAT関数は、結果セットが大きくなる可能性があるため、パフォーマンスに注意する必要があります。

MySQLとMariaDBでは、UNIONクエリとGROUP_CONCAT関数以外にも、複数の行のデータを1つの列にまとめる方法はいくつかあります。状況に応じて適切な方法を選択することで、パフォーマンスや可読性を向上させることができます。

代替方法

サブクエリ：サブクエリを使用して、複数の行のデータを1つの列にまとめることができます。この方法は、GROUP_CONCAT関数よりも柔軟性が高く、複雑な条件を処理することができます。

SELECT id,
       (SELECT GROUP_CONCAT(DISTINCT name SEPARATOR ', ')
        FROM orders
        WHERE customer_id = id) AS customer_orders
FROM customers;

ウィンドウ関数： MySQL 8.0以降では、ウィンドウ関数を使用して、現在の行とその周辺の行に基づいて値を処理することができます。この方法は、GROUP_CONCAT関数よりも効率的で、可読性も高い場合があります。

SELECT id,
       WM_CONCAT(DISTINCT name SEPARATOR ', ') OVER (PARTITION BY id ORDER BY name) AS customer_orders
FROM customers;

SELECT c.id,
       GROUP_CONCAT(o.product_name SEPARATOR ', ') AS customer_orders
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id;