MySQL选择不同的行到逗号分隔的列表列中?
您可以借助GROUP_CONCAT()函数来实现。语法如下-
SELECT yourColumnName1,yourColumnName2,yourColumnName3,..N, GROUP_CONCAT(yourColumnName4) as anyAliasName FROM yourTableName group by yourColumnName3, yourColumnName1,yourColumnName2;
为了理解上述语法,让我们创建一个表。创建表的查询如下-
mysql> create table CommaDelimitedList -> ( -> Id int NOT NULL AUTO_INCREMENT, -> Name varchar(10), -> GroupId int, -> CompanyName varchar(15), -> RefId int, -> PRIMARY KEY(Id) -> );
使用INSERT命令在表中插入一些记录。查询如下-
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',162);
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',5);
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Larry',5,'Google',4);
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
-> values('Sam',6,'Amazon',3);使用select语句显示表中的所有记录。查询如下-
mysql> select *from CommaDelimitedList;
以下是输出-
+----+-------+---------+-------------+-------+ | Id | Name | GroupId | CompanyName | RefId | +----+-------+---------+-------------+-------+ | 1 | Larry | 5 | Google | 162 | | 2 | Larry | 5 | Google | 5 | | 3 | Larry | 5 | Google | 4 | | 4 | Sam | 6 | Amazon | 3 | +----+-------+---------+-------------+-------+ 4 rows in set (0.00 sec)
这是执行定界列列表的查询-
mysql> select Name,GroupId,CompanyName, -> group_concat(RefId) as RefList -> from CommaDelimitedList -> group by CompanyName, Name,GroupId;
以下是输出-
+-------+---------+-------------+---------+ | Name | GroupId | CompanyName | RefList | +-------+---------+-------------+---------+ | Sam | 6 | Amazon | 3 | | Larry | 5 | Google | 162,5,4 | +-------+---------+-------------+---------+ 2 rows in set (0.00 sec)
热门推荐
1 狗狗技能训练
5 如何训练狗狗笑
6 狗狗训练排便
9 狗狗响片训练
10 如何训练狗狗不捡食
11 狗狗尿训练
12 怎么训练狗狗找东西
13 狗狗训练站立
14 狗狗训练飞盘
15 鞍山狗狗训练
16 怎样训练泰迪狗狗谢谢
17 狗狗外出训练
18 怎么训练金毛狗狗大小便