使用MySQL,我可以执行以下操作:

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

我的输出:

shopping
fishing
coding

但我只想要1行1列:

预期输出:

shopping, fishing, coding

原因是我从多个表中选择了多个值,在所有的连接之后,我得到了比我想要的多得多的行。

我在MySQL Doc上查找了一个函数,它看起来不像CONCAT或CONCAT_WS函数接受结果集。

这里有人知道怎么做吗?


当前回答

使用MySQL(5.6.13)会话变量和赋值运算符,如下所示

SELECT @logmsg := CONCAT_ws(',',@logmsg,items) FROM temp_SplitFields a;

然后你可以得到

test1,test11

其他回答

通过设置GROUP_CONCAT_max_len参数,可以更改GROUP_CONCAT值的最大长度。

请参阅MySQL文档中的详细信息。

试试看:

DECLARE @Hobbies NVARCHAR(200) = ' '

SELECT @Hobbies = @Hobbies + hobbies + ',' FROM peoples_hobbies WHERE person_id = 5;

TL;博士;

set @sql='';
set @result='';
set @separator=' union \r\n';
SELECT 
@sql:=concat('select ''',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME  ,''' as col_name,',
INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH ,' as def_len ,' ,
'MAX(CHAR_LENGTH(',INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME , '))as  max_char_len',
' FROM ',
INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
) as sql_piece, if(@result:=if(@result='',@sql,concat(@result,@separator,@sql)),'','') as dummy
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE 
INFORMATION_SCHEMA.COLUMNS.DATA_TYPE like '%char%'
and INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA='xxx' 
and INFORMATION_SCHEMA.COLUMNS.TABLE_NAME='yyy';
select @result;

本例中另一个有趣的例子-

下面是people_hobbies示例表的结构-

DESCRIBE people_hobbies;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int unsigned | NO   | PRI | NULL    | auto_increment |
| ppl_id  | int unsigned | YES  | MUL | NULL    |                |
| name    | varchar(200) | YES  |     | NULL    |                |
| hby_id  | int unsigned | YES  | MUL | NULL    |                |
| hobbies | varchar(50)  | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+

该表的填充方式如下-

SELECT * FROM people_hobbies;
+----+--------+-----------------+--------+-----------+
| id | ppl_id | name            | hby_id | hobbies   |
+----+--------+-----------------+--------+-----------+
|  1 |      1 | Shriya Jain     |      1 | reading   |
|  2 |      4 | Shirley Setia   |      4 | coding    |
|  3 |      2 | Varsha Tripathi |      7 | gardening |
|  4 |      3 | Diya Ghosh      |      2 | fishing   |
|  5 |      4 | Shirley Setia   |      3 | gaming    |
|  6 |      1 | Shriya Jain     |      6 | cycling   |
|  7 |      2 | Varsha Tripathi |      1 | reading   |
|  8 |      3 | Diya Ghosh      |      5 | shopping  |
|  9 |      3 | Diya Ghosh      |      4 | coding    |
| 10 |      4 | Shirley Setia   |      1 | reading   |
| 11 |      1 | Shriya Jain     |      4 | coding    |
| 12 |      1 | Shriya Jain     |      3 | gaming    |
| 13 |      4 | Shirley Setia   |      2 | fishing   |
| 14 |      4 | Shirley Setia   |      7 | gardening |
| 15 |      2 | Varsha Tripathi |      3 | gaming    |
| 16 |      2 | Varsha Tripathi |      2 | fishing   |
| 17 |      1 | Shriya Jain     |      5 | shopping  |
| 18 |      1 | Shriya Jain     |      7 | gardening |
| 19 |      3 | Diya Ghosh      |      1 | reading   |
| 20 |      4 | Shirley Setia   |      5 | shopping  |
+----+--------+-----------------+--------+-----------+

现在,生成了一个表hobby_list,其中包含所有人的列表和每个人的爱好列表,每个爱好在一行中-

CREATE TABLE hobby_list AS
    -> SELECT ppl_id, name,
    -> GROUP_CONCAT(hobbies ORDER BY hby_id SEPARATOR "\n")
    -> AS hobbies
    -> FROM people_hobbies
    -> GROUP BY ppl_id
    -> ORDER BY ppl_id;
SELECT * FROM hobby_list;

对于在这里查看如何将GROUP_NCAT与子查询一起使用的人,发布以下示例

SELECT i.*,
(SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist
FROM items i
WHERE i.id = $someid

因此,必须在子查询中使用GROUP_CONNAT,而不是包装它。

有一个GROUP聚合函数GROUP_CONCAT。