mysql - 如何使用 SQL 合并来自多个表的数据？

Question

我的 MySql 数据库中有以下表格:

+----------------------------------------------+
                      PROJECT
+----------------------------------------------|
+----------------------------------------------|
  project_id | team_size | from_date
+----------------------------------------------|
    1        |   34      |  1 Dec 2010
+----------------------------------------------|
    2        | 2         | 2 Jan 1902
+----------------------------------------------|
    3        | 99        | 15 Aug 1947
+----------------------------------------------+

---

+----------------------------------------------+
        Technologies
+----------------------------------------------+
+----------------------------------------------+
 technology_id | technology_name
+----------------------------------------------+
       1       | Java
+----------------------------------------------+
       2       | CPP
+----------------------------------------------+
       3       | Hibernate
+----------------------------------------------+
       4       | EJB
+----------------------------------------------+
       5       | Python
+----------------------------------------------+
       6       | Hadoop
+----------------------------------------------+
       7424    | Perl
+----------------------------------------------+

---

要链接 Project 和 Technologies 表，我有下表:

+----------------------------------------------+
        Project_Technologies
+----------------------------------------------+
+----------------------------------------------+
   Project_ID   | Technology_ID
+----------------------------------------------+
    1           | 2
+----------------------------------------------+
    1           | 7424
+----------------------------------------------+
    2           | 1
+----------------------------------------------+
    2           | 3
+----------------------------------------------+
    2           | 4
+----------------------------------------------+
    2           | 5
+----------------------------------------------+

我想在 UI 中以行和列的形式显示一个表中的数据。例如:

+---------------------------------------------------+
  project_id | team_size | from_date | technologies 
|---------------------------------------------------|
|     1      |   34      | 1 Dec 2010| CPP, Perl    |
|---------------------------------------------------|
|     2      |   2       | 2 Jan 1902| Java, Hibernate, EJB, Python |
|---------------------------------------------------|
|     3      |   99      |15 Aug 1947|              |
+---------------------------------------------------+

我无法形成 sql 查询来得到这样的东西。我尝试了以下查询，它给了我重复的行。

select pr.project_id,pr.team_size,pr.from_date,tech.technology_name
from project pr, project_technologies ptech, technologies tl
where pr.project_id=ptech.project_id and ptech.technology_id=tl.technology_id

我想知道如何避免重复行？目前这个查询在 project_id=1 时给了我 2 行，在 project_id=2 时给了我 4 行

Answer 1

这应该可以解决问题......一个 group by 和一个 group_concat

select 
    pr.project_id, 
    pr.team_size, 
    pr.from_date, 
    GROUP_CONCAT(tech.technology_name separator ', ') as technologies
from  
    project pr 
    JOIN project_technologies ptech ON pr.project_id=ptech.project_id
    JOIN technologies tl ON ptech.technology_id=tl.technology_id
GROUP BY
    pr.project_id, 
    pr.team_size, 
    pr.from_date

编辑已修复以包含 JOIN 子句