Birden çok satırı tek bir sütunda birleştirmek için MySQL GROUP_CONCAT kullanmak

28/02/2020 - MYSQL

Bu örnekte, hangi siparişlerin belirli kalemleri içerdiğini bulacağız. Kolay! Ancak, birden çok satır döndürmekten kaçınmak istiyoruz. Bunun yerine, sipariş kimliklerini virgül işareti ile birleştirip bir alanda tutacağız.


Veritabanı


Tablolar


mysql> DESCRIBE items;
+-------+------------------------+------+-----+---------+----------------+
| Field | Type                   | Null | Key | Default | Extra          |
+-------+------------------------+------+-----+---------+----------------+
| id    | int(11) unsigned       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100)           | NO   |     |         |                |
| price | decimal(11,2) unsigned | NO   |     | NULL    |                |
+-------+------------------------+------+-----+---------+----------------+

mysql> DESCRIBE orders;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| created_at | datetime         | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

mysql> DESCRIBE orders_items;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| orders_id | int(11) unsigned | NO   | MUL | NULL    |                |
| items_id  | int(11) unsigned | NO   | MUL | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

Veriler


mysql> SELECT * FROM items;
+----+----------+----------+
| id | name     | price    |
+----+----------+----------+
|  1 | bmw      | 20000.00 |
|  2 | mercedes | 25000.00 |
|  3 | audi     | 19000.00 |
|  4 | toyota   | 15000.00 |
|  5 | mazda    | 13000.00 |
+----+----------+----------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM orders;
+----+---------------------+
| id | created_at          |
+----+---------------------+
|  1 | 2010-01-01 00:00:00 |
|  2 | 2010-02-01 00:00:00 |
|  3 | 2010-03-01 00:00:00 |
+----+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM orders_items;
+----+-----------+----------+
| id | orders_id | items_id |
+----+-----------+----------+
|  1 |         1 |        1 |
|  2 |         1 |        2 |
|  3 |         1 |        3 |
|  4 |         2 |        1 |
|  5 |         3 |        1 |
|  7 |         3 |        4 |
| 10 |         2 |        5 |
+----+-----------+----------+
7 rows in set (0.00 sec)

Sorgu


mysql> SELECT
    -> items.name AS ItemName,
    -> GROUP_CONCAT(orders.id SEPARATOR ',') AS Orders
    -> FROM orders
    -> INNER JOIN orders_items ON orders.id = orders_items.orders_id
    -> INNER JOIN items ON orders_items.items_id = items.id
    -> WHERE items.name = "bmw" OR items.name = "mercedes" OR items.name = "audi"
    -> GROUP BY items.id;

Sonuç


+----------+--------+
| ItemName | Orders |
+----------+--------+
| bmw      | 2,1,3  |
| mercedes | 1      |
| audi     | 1      |
+----------+--------+
3 rows in set (0.00 sec)