如何使用SQL合并来自多个表的数据? [英] How to combine data from multiple tables using SQL?

问题描述

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

I have following tables in my MySql database :

+----------------------------------------------+
                      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中行和列的形式在一个表中显示数据.例如:

I want to show data in one table in the form of rows and columns in UI. For example :

+---------------------------------------------------+
  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查询来获得类似这样的信息.我尝试了以下查询，这给了我重复的行.

I am not able to form sql query to get something like this. I have tried following query which is giving me duplicate rows.

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行

I would like to know how to avoid duplicate rows? Currently this query is giving me 2 rows when project_id=1 and 4 rows when project_id=2

推荐答案

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

This should do the trick... a group by and a 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子句

EDIT fixed to include JOIN clause

这篇关于如何使用SQL合并来自多个表的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！