我有两个表:
table_a:
ID | NAME
===============
1 | DAN
2 | RON
3 | JANE
table_b:
ID | TEXT
===============
2 | APPLE
2 | BANANA
2 | COFFEE
我需要获取 ID 2 的结果。我在想,即使我从 ID 2 那里得到一个包含 TEXT 值的字符串table_b
也会很棒,例如:
ID | NAME | TEXT
=============================
2 | RON | APPLE,BANANA,COFFEE
这可能吗?
SELECT
A.*,GROUP_CONCAT(B.`TEXT`) `TEXT`
FROM
table_a A INNER JOIN table_b B USING (ID)
WHERE A.ID=2;
这是一个示例
mysql> DROP TABLE IF EXISTS table_a;
Query OK, 0 rows affected (0.03 sec)
mysql> DROP TABLE IF EXISTS table_b;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE table_a
-> (ID int not null auto_increment,
-> NAME VARCHAR(20),primary key (ID));
Query OK, 0 rows affected (0.13 sec)
mysql> CREATE TABLE table_b
-> (ID int not null,`TEXT` TEXT,key (ID));
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO table_a (NAME) VALUES ('DAN'),('RON'),('JANE');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO table_b (ID,`TEXT`) VALUES
-> (2,'APPLE'),(2,'BANANA'),(2,'COFFEE');
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT A.*,GROUP_CONCAT(B.`TEXT`) `TEXT`
-> FROM table_a A INNER JOIN table_b B USING (ID) WHERE A.ID=2;
+----+------+---------------------+
| ID | NAME | TEXT |
+----+------+---------------------+
| 2 | RON | APPLE,BANANA,COFFEE |
+----+------+---------------------+
1 row in set (0.00 sec)
试一试!!
要获取 table_a 中的所有可用值以及可以从table_b分组的任何值,请执行以下操作:
SELECT
A.*,IFNULL(GROUP_CONCAT(B.`TEXT`),'') `TEXT`
FROM
table_a A LEFT JOIN table_b B USING (ID)
GROUP BY A.ID
下面是该示例:
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS table_a;
Query OK, 0 rows affected (0.03 sec)
mysql> DROP TABLE IF EXISTS table_b;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE table_a (ID int not null auto_increment,NAME VARCHAR(20),primary key (ID));
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE table_b (ID int not null,`TEXT` TEXT,key (ID));
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO table_a (NAME) VALUES ('DAN'),('RON'),('JANE');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO table_b (ID,`TEXT`) VALUES (2,'APPLE'),(2,'BANANA'),(2,'COFFEE');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT A.*,IFNULL(GROUP_CONCAT(B.`TEXT`),'') `TEXT`
-> FROM table_a A LEFT JOIN table_b B USING (ID) GROUP BY A.ID;
+----+------+---------------------+
| ID | NAME | TEXT |
+----+------+---------------------+
| 1 | DAN | |
| 2 | RON | APPLE,BANANA,COFFEE |
| 3 | JANE | |
+----+------+---------------------+
3 rows in set (0.00 sec)
mysql>
你只需要使用GROUP_CONCAT
SELECT a.ID, a.Name, GROUP_CONCAT(b.text) textList
FROM table_a a
INNER JOIN table_b b
ON a.ID = b.ID
-- WHERE ...if you want extra condition.
GROUP BY a.ID, a.Name
- SQLFiddle 演示
- GROUP_CONCAT
您可以使用MySQL中的GROUP_CONCAT
函数来组合相关表中的结果:
select
a.id,
a.name,
group_concat(b.text) text
from
table_a a
join
table_b b on a.id = b.id
group by a.id
示例输出:
| ID | NAME | TEXT |
------------------------------------
| 2 | RON | APPLE,BANANA,COFFEE |
演示:http://www.sqlfiddle.com/#!2/e3584/9