MySQL中的空连接上的group_concat [英] group_concat on an empty join in MySQL

问题描述

我遇到以下问题: 我有两个表:(简体)

I've got the following problem: I have two tables: (simplified)

+--------+    +-----------+
| User   |    | Role      |
+--------+    +-----------+
| ID<PK> |    | ID <PK>   |
+--------+    | Name      |
              +-----------+

与它们之间的M:N关系

and M:N relationship between them

+-------------+   
| User_Role   |   
+-------------+
| User<FK>    |
| Role<FK>    |
+-------------+

我需要创建一个视图，该视图选择我:用户，并在一栏中列出其所有角色(由group_concat完成).

I need to create a view, which selects me: User, and in one column, all of his Roles (this is done by group_concat).

我尝试了以下操作:

SELECT u.*, group_concat(r.Name separator ',') as Roles FROM 
  User u
  LEFT JOIN User_Role ur ON ur.User=u.ID
  LEFT JOIN Role r ON ur.Role=r.ID
 GROUP BY u.ID;

但是，这对于具有 some 个定义角色的用户有效.没有角色的用户将不会返回.当用户没有任何角色时，如何修改语句以返回角色"列中带有空字符串的用户?

However, this works for an user with some defined roles. Users without role aren't returned. How can I modify the statement, to return me User with empty string in Roles column when User doesn't have any Role?

说明:我将SQL数据直接传递给网格，然后网格对其进行格式化，与在代码中进行格式化相比，创建缓慢而复杂的视图相对容易.

Explanation: I'm passing the SQL data directly to a grid, which then formats itself, and it is easier for me to create slow and complicated view, than to format it in my code.

我正在使用MySQL

I'm using MySQL

推荐答案

我尝试了您的SQL并且没有pb来运行它，即使没有角色的用户也返回了所有行，唯一的问题是您将null角色列，以避免在字段为NULL时使用COALESCE函数获取空字符串.

I have try your SQL and have no pb to run it , all row are returned even user without role, the only thing that you have null into the roles column, to avoid that use COALESCE function to get an empty string when the field will be NULL.

SELECT u.*, group_concat(COALESCE(r.Name, "") separator ',') as Roles FROM 
  User u
  LEFT JOIN User_Role ur ON ur.User=u.ID
  LEFT JOIN Role r ON ur.Role=r.ID
 GROUP BY u.ID;

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