通过合并类似的方法来操作重复的(是否可能)

Question

我有第1行和第2行，除了status列之外，它们在记录中是相似的。

   +-----+------+-------+-----------+----------+---------+
| #id | NAME | STATUS|   c_lan   | Java_lan | Dot_lan |
+-----+------+-------+-----------+----------+---------+
|   1 |  A   |   111 |      yes  |    Q     | W       |
|   1 |  A   |   222 |      yes  |    Q     | W       |
+-----+------+-------+-----------+----------+---------+

现在是否有可能将这两行(1,2)变成如下所示的一行(需要将状态值合并到一个单元格中)？

+-----+------+-----------+-----------+----------+---------+
| #id | NAME | STATUS    |   c_lan   | Java_lan | Dot_lan |
+-----+------+-----------+-----------+----------+---------+
|   1 | A    |   111,222 |      yes  |    Q     | W       |
+-----+------+-----------+-----------+----------+---------+

注意到我认为这是在mysql视图上完成的。

Answer 1

为此使用组_凹()。

您的桌子：

create table foo
(
id int,
name varchar(10),
status int,
c_lan varchar(10),
Java_lan varchar(10),
Dot_lan varchar(10)
);

insert into foo values (  1,'A',111,'yes','Q','W'   );

insert into foo values (  1,'A',222,'yes','Q','W'   );

Testcase：

mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan from foo group by id;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

查看：

create view concatview as (
select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
from foo
group by id
);

测试：

mysql> select * from concatview;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
+------+---------+-------+----------+---------+
1 row in set (0.00 sec)

mysql>

显然，您可能希望GROUP不仅仅是id (取决于您认为是“复制”的是什么)，并且可能希望以某种方式订购GROUP_CONCAT()，但这将指向正确的方向。

编辑以解决您的评论：

mysql> insert into foo values ( 2,'b',555,'yes','Q','W' );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> select id, GROUP_CONCAT(status separator ',') as status, c_lan, Java_lan, Dot_lan
    -> from foo
    -> group by id
    -> ;
+------+---------+-------+----------+---------+
| id   | status  | c_lan | Java_lan | Dot_lan |
+------+---------+-------+----------+---------+
|    1 | 111,222 | yes   | Q        | W       |
|    2 | 555     | yes   | Q        | W       |
+------+---------+-------+----------+---------+
2 rows in set (0.01 sec)

mysql>