将多行合并到MySQL中以逗号分隔的列表中？
要将多行合并为一个逗号分隔的列表，请使用GROUP_CONCAT()方法。让我们首先创建一个表-
create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Name varchar(30), Marks int );
使用插入命令在表中插入一些记录-
insert into DemoTable(Name,Marks) values('John',67);
insert into DemoTable(Name,Marks) values('Carol',69);
insert into DemoTable(Name,Marks) values('Sam',69);
insert into DemoTable(Name,Marks) values('David',78);
insert into DemoTable(Name,Marks) values('Chris',69);
insert into DemoTable(Name,Marks) values('Bob',67);使用select语句显示表中的所有记录-
select *from DemoTable;
输出结果
+----+-------+-------+ | Id | Name | Marks | +----+-------+-------+ | 1 | John | 67 | | 2 | Carol | 69 | | 3 | Sam | 69 | | 4 | David | 78 | | 5 | Chris | 69 | | 6 | Bob | 67 | +----+-------+-------+ 6 rows in set (0.00 sec)
以下是将多行合并为MySQL中逗号分隔列表的查询-
SELECT Marks, GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ', ') from DemoTable GROUP BY Marks;
输出结果
+-------+-----------------------------------------------------+ | Marks | GROUP_CONCAT(Name ORDER BY Name ASC SEPARATOR ', ') | +-------+-----------------------------------------------------+ | 67 | Bob, John | | 69 | Carol, Chris, Sam | | 78 | David | +-------+-----------------------------------------------------+ 3 rows in set (0.00 sec)