MySQL查询：来自单个单元格中多个单元格的值

There is a table name agents which contains a list of agents and another table named projects, which contain list of projects and IDs of agent working on that project.

有一个表名代理,其中包含一个代理列表和另一个名为projects的表,其中包含项目列表和在该项目上工作的代理的ID。

The output I am looking for is a list of agents, a total count of projects they are managing and list of projects.

我正在寻找的输出是一个代理列表,他们正在管理的项目总数和项目列表。

Sample tables:

agents

AgentID     AgentName
------      ---------
1           Name1
2           Name2
3           Name3

projects

ProjectID    ProjectName   agentID
---------    -----------   -------
1            PName1        2
2            PName2        3
3            PName3        2

Desired output:

AgentName   No. of Projects   ProjectNames
---------   ---------------   ------------
Name2       2                 PName1
                              PName3
Name3       1                 PName2

Using the below query I am able to concatenate all projects in one cell..

使用下面的查询,我能够连接一个单元格中的所有项目..

SELECT GROUP_CONCAT(projectName SEPARATOR ' , ') AS project_name FROM projects

But how can I concatenate projects linked to 1 agent... (as shown in the desired output table)?

但是如何连接与1个代理链接的项目...(如所需的输出表所示)?

Is there any solution to this problem?

有没有解决这个问题的方法?

I'll appreciate any help from this community...

我将感谢这个社区的任何帮助......

1 个解决方案

#1

SELECT GROUP_CONCAT(projectName SEPARATOR ' , ') AS project_name, a.AgentID
FROM   projects p
       join agents a on (p.AgentID = a.AgentID)
GROUP BY a.AgentID