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

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

我的输出:

shopping
fishing
coding

但我只想要1行1列:

预期输出:

shopping, fishing, coding

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

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

这里有人知道怎么做吗?


当前回答

试试看:

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;

其他回答

使用GROUP_NCAT:

SELECT GROUP_CONCAT(hobbies) FROM peoples_hobbies WHERE person_id = 5;

您可以使用GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

正如Ludwig在评论中所说,您可以添加DISTINCT运算符以避免重复:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

正如Jan在他们的评论中所说,您也可以在使用ORDER BY将值内爆之前对其进行排序:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

正如Dag在评论中所说,结果有1024字节的限制。要解决此问题,请在查询之前运行此查询:

SET group_concat_max_len = 2048;

当然,您可以根据需要更改2048。要计算和分配值:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

有一个GROUP聚合函数GROUP_CONCAT。

在我的例子中,我有一行ID,必须将其转换为char,否则,结果被编码为二进制格式:

SELECT CAST(GROUP_CONCAT(field SEPARATOR ',') AS CHAR) FROM table

试试看:

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;