MySQL组在双连接上进行连接 [英] Mysql group concat on double join

问题描述

我有一个用户表，我希望从中获得所有值，所以我有以下查询：

  SELECT tbl_user。* FROM tbl_user 
  

现在，我想在此结果中再增加一列，以显示该用户具有的所有角色，（或不显示任何内容如果该用户没有角色）。角色信息来自另外两个表。

第一个表包含以下两个值：userid，roleid
第二个表包含roleid和role_name。

因此，concat组需要根据表1中的角色ID获取所有角色名称。

我尝试了几种不同的方法为此，但我没有成功。要么我得到的结果只有一个相同的角色名，要么根本没有结果。

感谢您的帮助

Michael

解决方案

更新：为具有以下功能的用户添加了 LEFT JOIN

  SELECT 
 tbl_user。*，
 GROUP_CONCAT（role_name）AS角色
从
 tbl_user左加入tbl_roles到tbl_user.userid = tbl_roles.userid 
加入tbl_rolenames到tbl_roles.roleid = tbl_rolenames.roleid 
 GROUP BY tbl_user.userid 
  

请注意，MySQL允许的 GROUP BY 列数少于<$ c总共$ c> SELECT 个列表，但在其他RDBMS中，您需要显式列出 tbl_user 中的列，并将它们包括在 GROUP BY ，或者对 tbl_user 进行额外的自我联接，以从该表中获取剩余的列。

类似的东西：

 选择
 urole.userid，
 uall.username，
 uall.name，
 uall.othercols，
 urole.roles 
 FROM 
 tbl_user uall JOIN（
选择
 tbl_user.userid，
 GROUP_CONCAT（role_name）AS角色
 FROM 
 tbl_user左加入tbl_roles ON tbl_user.userid = tbl_roles.roleid 
在tbl_roles.roleid = tbl_rolenames.roleid上加入tbl_rolenames 
由tbl_user.userid 
分组p> 
I have a user table from which I want all values, so I have this query:
SELECT tbl_user.* FROM tbl_user
Now I want one additional column in this result which shows all roles this user has, (or nothing if there are no roles for the user). The role information comes from two additional tables.

The first table contains these two values: userid, roleid
The second table contains roleid and role_name.

So the group concat needs to get all role names based on the roleid's in table1.

I have tried several different ways to do this, but I don't succeed. Either I get only one result with several times the same rolename, or no result at all.

Thanks for your help

Michael
解决方案
Update: added LEFT JOIN for users with no role.
SELECT
  tbl_user.*,
  GROUP_CONCAT(role_name) AS roles
FROM 
  tbl_user LEFT JOIN tbl_roles ON tbl_user.userid = tbl_roles.userid
  JOIN tbl_rolenames ON tbl_roles.roleid = tbl_rolenames.roleid
GROUP BY tbl_user.userid
Note that MySQL will permit a GROUP BY on fewer columns than appear in the SELECT list in total, but in other RDBMS you would need to explicitly list out the columns in tbl_user and include them in the GROUP BY, or do an additional self join against tbl_user to get the remaining columns from that table.

Something like:
SELECT 
  urole.userid,
  uall.username,
  uall.name,
  uall.othercols,
  urole.roles
FROM
  tbl_user uall JOIN (
  SELECT 
    tbl_user.userid, 
    GROUP_CONCAT(role_name) AS roles
  FROM 
    tbl_user LEFT JOIN tbl_roles ON tbl_user.userid = tbl_roles.roleid
    JOIN tbl_rolenames ON tbl_roles.roleid = tbl_rolenames.roleid
  GROUP BY tbl_user.userid
) urole ON uall.userid = urole.userid


                        
这篇关于MySQL组在双连接上进行连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！