加入在SQL三个表

Question

User 
uid name 

Roles 
uid role_id 

Subscriptions 
uid subscriptions_id 

好吧，这似乎太容易了，但我们都没有得到所希望的结果，应该是这样的：

user roles subscriptions 
1 2,3 1,4,5 
2 2,4 4,5,6 
.... 

相反，结果是这样的：

user roles subscriptions 
1 2,3 5 
2 2,4 6 
.... 

的选择命令如下：

SELECT User.name, 
     GROUP_CONCAT(Roles.role_id), 
     GROUP_CONCAT(Subscriptions.subscriptions_id) 
FROM User 
     LEFT JOIN Roles ON User.uid = Roles.uid 
     LEFT JOIN Subscriptions ON Users.uid = Subscriptions.uid 

顺便说一句 - GROUP_CONCAT不是问题。如果我离开了这一点，我还是得到这样的：

user roles subscriptions 
1 2  5 
1 3  5 
2 2  6 

，而不是

user roles subscriptions 
1 2  1 
1 3  4 
1 -  5 

Answer 1

我尝试这样做：

SELECT User.uid, 
     GROUP_CONCAT(DISTINCT Roles.role_id), 
     GROUP_CONCAT(DISTINCT Subscriptions.subscriptions_id) 
FROM User 
     LEFT JOIN Roles ON User.uid = Roles.uid 
     LEFT JOIN Subscriptions ON User.uid = Subscriptions.uid 
GROUP BY User.uid 

，我得到这个：

user roles subscriptions 
1  2,3  1,5,4 
2  2,4  4,5,6 

是你的结果吗？

Answer 2

select 
u.uid, u.name, r.roles_grp_concat, s.subs_grp_concat 
from 
users u 
left outer join 
(
    select uid, group_concat(role_id) as roles_grp_concat 
    from roles group by uid 
) r on r.uid = u.uid 
left outer join 
(
    select uid, group_concat(subscriptions_id) as subs_grp_concat 
    from subscriptions group by uid 
) s on s.uid = u.uid 
order by 
u.uid;