mysql - 如何将多个明细行的列汇总为单个列？

我有两张桌子：

table_a:

ID    |    NAME
===============
1     |    DAN
2     |    RON
3     |    JANE

table_b:

ID    |    TEXT
===============
2     |    APPLE
2     |    BANANA
2     |    COFFEE

我需要得到身份证2的结果。
我在想，即使我从table_b得到一个包含ID 2的文本值的字符串，也会很好，例如：

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)

试试看！!!
要获取表a中的所有可用值以及可以从表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>

关于mysql - 如何将多个明细行的列汇总为单个列？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13942606/