dpzbzp8728
2015-12-03 05:27
浏览 40
已采纳

使用单个查询连接两个表并显示第一个表行的第二个表行数据逗号分隔

I need to output row data from a table while joining with another table and column data in second table should be comma seperated for each first table row by a single query. If there is no matching rows in second table, It should not in output. Following are the tables.

users table
╔══════════╦═════════════════╗
║ user_id  ║    username     ║
╠══════════╬═════════════════╣
║ 1        ║ abc             ║
║ 2        ║ def             ║
║ 3        ║ ghi             ║
║ 4        ║ jkl             ║
╚══════════╩═════════════════╝

users_friends table
╔══════════╦═════════════════╗
║ user_id  ║    friend_id    ║
╠══════════╬═════════════════╣
║ 1        ║ 1               ║
║ 1        ║ 2               ║
║ 1        ║ 3               ║
║ 2        ║ 1               ║
║ 2        ║ 3               ║
║ 3        ║ 4               ║
╚══════════╩═════════════════╝

I want the output is as follows.

╔══════════╦═════════════════╗═════════════════╗
║ user_id  ║    user_name    ║       friends   ║
╠══════════╬═════════════════╣═════════════════╣
║ 1        ║ abc             ║  1,2,3          ║
║ 2        ║ def             ║  1,3            ║
║ 3        ║ ghi             ║  4              ║
╚══════════╩═════════════════╝═════════════════╝

I tried query as follows. But it repeat data. But It is not working.

$sql= "SELECT user_id, user_name, friend_id FROM users INNER JOIN users_friends ON users.user_id= friends.user_id"
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douxing9228 2015-12-03 05:30
    已采纳

    Use the MySQL GROUP_CONCAT() function:

    SELECT u.user_id, u.user_name, GROUP_CONCAT(uf.friend_id)
    FROM users u INNER JOIN users_friends uf ON u.user_id = uf.user_id
    GROUP BY user_id, user_name
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题