在这个例子中，我们将列出所有的客户及其相关的账户，但只列在一行，而不是多行。
数据库
桌子
CREATE TABLE `customers` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`created_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `accounts` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`customers_id` int(11) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `FK_customers` (`customers_id`),
CONSTRAINT `FK_customers` FOREIGN KEY (`customers_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
数据
mysql> SELECT * FROM customers;
+----+------+---------------------+
| id | name | created_at |
+----+------+---------------------+
| 1 | Bob | 2010-01-01 00:00:00 |
| 2 | Joe | 2010-01-01 00:00:00 |
| 3 | Leo | 2010-01-02 00:00:00 |
| 4 | Edi | 2009-01-01 00:00:00 |
+----+------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM accounts;
+----+---------+--------------+
| id | type | customers_id |
+----+---------+--------------+
| 1 | Current | 1 |
| 2 | Current | 2 |
| 3 | ISA | 1 |
| 4 | Savings | 4 |
+----+---------+--------------+
4 rows in set (0.00 sec)
查询
这是有多条记录的查询：
mysql> SELECT
-> c.id,
-> c.name,
-> c.created_at,
-> a.type
-> FROM customers c
-> LEFT JOIN accounts a ON c.id = a.customers_id
-> ORDER BY c.created_at DESC;
+----+------+---------------------+---------+
| id | name | created_at | type |
+----+------+---------------------+---------+
| 3 | Leo | 2010-01-02 00:00:00 | NULL |
| 1 | Bob | 2010-01-01 00:00:00 | Current |
| 2 | Joe | 2010-01-01 00:00:00 | Current |
| 1 | Bob | 2010-01-01 00:00:00 | ISA |
| 4 | Edi | 2009-01-01 00:00:00 | Savings |
+----+------+---------------------+---------+
5 rows in set (0.00 sec)
这是合并后的版本：
mysql> SELECT
-> c.id,
-> c.name,
-> c.created_at,
-> GROUP_CONCAT(a.type SEPARATOR ',') AS accounts
-> FROM customers c
-> LEFT OUTER JOIN accounts a ON c.id = a.customers_id
-> GROUP BY c.id
-> ORDER BY c.created_at DESC;
+----+------+---------------------+-------------+
| id | name | created_at | accounts |
+----+------+---------------------+-------------+
| 3 | Leo | 2010-01-02 00:00:00 | NULL |
| 1 | Bob | 2010-01-01 00:00:00 | Current,ISA |
| 2 | Joe | 2010-01-01 00:00:00 | Current |
| 4 | Edi | 2009-01-01 00:00:00 | Savings |
+----+------+---------------------+-------------+
4 rows in set (0.01 sec)
