在这个例子中，我们要找出哪些订单中包含某些项目。 这很容易！我们可以从这些订单中找到答案。然而，我们希望避免返回多行。相反，我们将用逗号串联订单ID，并将其存储在一个字段中。
数据库
桌子
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 | |
+-----------+------------------+------+-----+---------+----------------+
数据
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)
查询
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;
结果
+----------+--------+
| ItemName | Orders |
+----------+--------+
| bmw | 2,1,3 |
| mercedes | 1 |
| audi | 1 |
+----------+--------+
3 rows in set (0.00 sec)
