来自Group_Concat的SQL单独表键

Question

I'm trying to separate the IDPosts column as a single row for each ID (attempting to create a favorite post list). The column next to it has all of the proper columns listed but are grouped, I want to obtain a row for each one grouped together in the favuserposts column. See my picture and query below. Please let me know if you need more information.

Displays everything but cannot display multiple favorite post IDs:

SELECT Users.IDUser, 
       Users.username, 
       Users.profile_picture, 
       Favorites.IDPosts,
       GROUP_CONCAT(DISTINCT coalesce(Favorites.IDPosts,'')) as "favuserposts", 
       ( Select body 
         from Posts 
         where Favorites.IDPosts=Posts.IDPosts 
          AND Users.IDUser=Favorites.IDUser)    
FROM Users
    LEFT OUTER JOIN Favorites ON Favorites.IDUser = Users.IDUser
GROUP BY Users.IDUser HAVING COUNT(IDPosts)>0;

Answer 1

You group by IDUser. So all other fields you select are aggregates in some way. Either you specify the aggregate you want (MIN, MAX, GROUP_CONCAT, etc.) or you get a random match. So let's look at what you select:

• Users.username = a "random" username for the user id, but as there is just one name per userid of course, you get the one user name for the id
• Users.profile_picture = same as username; you get the one picture for the user
• Favorites.IDPosts = a random one of the user's post IDs
• GROUP_CONCAT(DISTINCT coalesce(Favorites.IDPosts,'')) = string of all distinct IDs
• ( Select body from Posts ...) = a random one of the user's post bodies

So if you want a string listing all bodies, then use group_concat, just as you do with the post IDs:

GROUP_CONCAT(DISTINCT
( Select body 
  from Posts 
  where Favorites.IDPosts=Posts.IDPosts 
  AND Users.IDUser=Favorites.IDUser
)) as bodies