MySQL 连接子查询的输出？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3812864/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
concat the output of the subquery?
提问by Vijay
i have a query which would return values but i need them as a single output separated by commas..
我有一个会返回值的查询，但我需要它们作为由逗号分隔的单个输出..
So i tried to concat the output with the comma but it didn't work?
所以我试图用逗号连接输出，但它没有用？
select id from videos where duration=0; /// this would return some rows
I tried concat and concat_ws but didn't work
我试过 concat 和 concat_ws 但没有用
select concat(select concat(id,',') from videos where duration=0);
select concat((select id from videos where duration=0),',');
select concat_ws(',',(select id from videos where duration=0));
i need the id's of all rows with the comma separtor
我需要带有逗号分隔符的所有行的 ID
for example the output should be 1,4,6,78,565
例如输出应该是 1,4,6,78,565
any ideas?
有任何想法吗？
回答by Martin Smith
This is what group_concatdoes.
这就是group_concat所做的。
select group_concat(id) as video_list
from videos
where duration=0
回答by Chinmayee G
Try using GROUP_CONCAT
尝试使用 GROUP_CONCAT
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
Reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
参考：http: //dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
回答by Julien Hoarau
Use group_concat:
使用group_concat：
This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
此函数返回一个字符串结果，其中包含来自组的串联非 NULL 值。如果没有非 NULL 值，则返回 NULL。
SELECT
GROUP_CONCAT(id)
FROM
videos
WHERE
duration=0
回答by Omn
To work around the inability to use LIMITwith GROUP_CONCATyou can group by the results of a sub query
要解决无法使用的问题LIMIT，GROUP_CONCAT您可以按子查询的结果进行分组
This query breaks up your video ids into groups of 20 (but it could be REALLY slow on some datasets)
此查询将您的视频 ID 分成 20 个一组（但在某些数据集上可能会非常慢）
select group_concat(v.id) as video_list
from videos as v
where v.duration=0
group by (
select floor(count(v2.id)/20)
from videos as v2
where v2.duration=0
and v2.id <= v.id
)
Alternatively, if you have a lot of results but don't want such a slow query, you can increase to the size of your group_concat_max_len(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_group_concat_max_len) up to your max_allowed_packet(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet), if this is still not long enough, you would have to increase the max_allowed_packet size as well.
或者，如果您有很多结果但不想要这么慢的查询，您可以增加到您的group_concat_max_len( https://dev.mysql.com/doc/refman/8.0/en/server-system- variables.html#sysvar_group_concat_max_len）直到你的max_allowed_packet（https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet），如果这还不够长，你会有也增加 max_allowed_packet 大小。
