需要在选择语句中连接子查询的结果 [英] Need to concatenate results of a subquery in a select statement

问题描述

我有三个桌子

Table1: Users
Columns: User_ID (int), FirstName, LastName....
Values
1            Jane        Doe
2            John        Doe
3            Mike        Smith

Table2: User_Groups
Columns: User_ID (int), Group_ID(int)
Values:
Row1: 1          2  
Row2: 1          3
Row3: 2          1
Row4: 2          3
Row5: 3          1

Table3: Groups
Columns: Group_ID (int), GroupName(varchar)
Values
Row1: 1         Admin
Row2: 2         Power User
Row3: 3         Developer

我想创建一个查询，该查询可以通过以下方式返回结果: **结果

I would like to create a query that can return the results in the following way: **RESULT

UserID GroupNames
Row1: 1      Power User, Developer
Row2: 2      Admin, Developer
Row3: 3      Admin

在SQL Server中-我可以使用以下方法实现此目标:

In SQL Server - I was able to achieve it using something like this:

SELECT User_ID,
  SUBSTRING( 
     replace( 
         replace( 
     (SELECT Groups.GroupName
  FROM User_Groups, Groups 
  where groups.Group_ID = 
                  User_Groups.Group_ID AND
                User_Groups.User_ID =Users.User_ID
          FOR XML PATH('') )
          ,'<GROUPNAME>',', ')
      ,'</GROUPNAME>',''),3,2000) as UserGroups  
FROM User_Groups LEFT JOIN Groups ON 
User_Groups.Group_ID=Groups.Group_ID
ORDER BY User_ID ASC

我想在MySQL中获得类似的最终结果(尝试了GROUP_CONCAT等)，但未成功..如何在MySQL中获得类似的**结果.请注意，表已经存在，我无法更改它们. 任何帮助将不胜感激

I wanted to do get a similar final result in MySQL (tried GROUP_CONCAT etc) but unsuccessful.. how can I get similar **RESULT in MySQL. Please note the tables exist already and I cant change them. Any help will be greatly appreciated

推荐答案

这有效:

SELECT
    t1.User_ID AS UserID,
    (
        SELECT
            GROUP_CONCAT(t2.GroupName)
        FROM
            Groups t2
        WHERE
            t2.Group_ID IN(
                    SELECT
                        t3.Group_ID
                    FROM
                        User_Groups t3
                    WHERE
                        t3.iUser_ID = t1.User_ID
            )
    ) AS GroupNames
FROM
    Users t1

这看起来是个更好的主意，因为您不想拥有用户名，因此不需要涉及Users表:

And this look like better idea, since you don't want to have user names, so that's no need to involve Users table:

SELECT
    User_ID,
    GROUP_CONCAT(GroupName) AS GroupNames
FROM
    (
        SELECT
            t2.User_ID AS User_ID,
            t3.GroupName AS GroupName
        FROM
            User_Groups t2
        LEFT JOIN
            Groups t3 ON (t3.Group_ID = t2.Group_ID)
    ) tmp
GROUP BY
    User_ID

这篇关于需要在选择语句中连接子查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！