使用group_concat的查询只返回一行 [英] Query using group_concat is returning only one row
问题描述
这是一个查询,它应该获取用户的信息,项目的信息,以及与此项目关联的所有图像路径的group_concat。
This is a query that is supposed to get the user's information, their project's information, and a group_concat of all the image paths that such project is associated to. To add to this, I am only getting the information mentioned from the people the user is following.
但是,只能重新调整一行。
This is, however, only retunring one row.
SELECT users.first_name, users.last_name, users.user_id, projects.project_id, projects.project_name, projects.project_time, group_concat(images.image_path)
FROM users, projects, images
WHERE users.user_id = projects.user_id
AND users.user_id IN (SELECT follow_user_2 FROM following WHERE follow_user_1 = 1)
ORDER BY projects.project_id DESC
TO COMPARE: WORKS 意味着在循环中,它会提供所有用户的信息和与此类用户相关的项目信息。
TO COMPARE: The following query WORKS in the sense that in the loop it gives all of the user's information and the projects information related to such user.
SELECT users.first_name, users.last_name, users.user_id, projects.project_id, projects.project_name, projects.project_time
FROM users, projects
WHERE users.user_id = projects.user_id
AND users.user_id IN (SELECT follow_user_2 FROM following WHERE follow_user_1 = 1)
ORDER BY projects.project_id DESC
当我尝试使用group_concat时,它只返回一个行,我不明白为什么。
When I try to use group_concat it just returns me one row and I do not understand why.
请帮帮我?谢谢。如果我的问题不够清楚,我会详细说明。
Can someone help me please? Thank you. If my question was not clear enough, I will elaborate.
如果这有帮助,这里是一个SQL FIDDLE。
http://www.sqlfiddle.com/#!2/867f6/2
我不得不缩短我的架构很多。
If this helps, here's an SQL FIDDLE. http://www.sqlfiddle.com/#!2/867f6/2 I had to shorten my schema a lot. Try both queries to above to see the problem.
推荐答案
当我尝试使用group_concat时返回一行,我不明白为什么。
When I try to use group_concat it just returns me one row and I do not understand why.
因为你没有使用 GROUP BY
子句。当使用
GROUP_CONCAT
的聚合函数时,您需要告诉数据库您希望数据合并的列。
Because you have not used the GROUP BY
clause in your query. When using aggregate functions like GROUP_CONCAT
you need to tell the database about the column using which you want your data to be combined.
目前,您的查询会对所有记录进行分组,并在输出中提供1条记录。
Currently your query is grouping all records and giving 1 record in the output.
如果添加 GROUP BY users.userid
在查询,然后记录将按唯一userid的组。我更新了你的小提琴,它现在给了2条记录: http://www.sqlfiddle.com/#!2/867f6 / 18>
If you add GROUP BY users.userid
in the query then the records will be grouped by unique userid's. I updated your fiddle and it now gives 2 records: http://www.sqlfiddle.com/#!2/867f6/18
请注意:在标准SQL查询中,GROUP BY子句中列出的列应与SELECT子句中的列匹配(聚合函数除外) 。
Please note: In standard SQL queries, columns listed in the GROUP BY clause should match the column in the SELECT clause (except the aggregate functions).
这篇关于使用group_concat的查询只返回一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!