dpiuqwwyu187975836 2014-07-03 21:19
浏览 52
已采纳

来自Group_Concat的SQL单独表键

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.

enter image description here

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;
  • 写回答

1条回答 默认 最新

  • dsijovl015728613 2014-07-04 00:20
    关注

    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 
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥50 使用ADC0809 与 51 单片机设计电路以实现显示电压模拟值
  • ¥15 QGC打开没有地图显示,离线地图也不显示,如何解决?
  • ¥20 Android Studio 的 webview 与访问网络存在的限制
  • ¥15 某帖子的数据集不清楚来源,求帮助
  • ¥15 idea构建mod报错无效的源发行版项目链接,如何解决?
  • ¥15 springboot中的路径问题
  • ¥80 App Store Connect 中设置了订阅项目,Xcode 中预览可以正确显示价格,真机测试却无法显示
  • ¥15 MATLAB的PIV算法问题
  • ¥15 RflySim例程学习:matlab编译报错
  • ¥20 谁来给我解答一下疑惑
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部