将多个表中的数据合并为单行，同时连接一些值 [英] Getting data from multiple tables into single row while concatenating some values

问题描述

我尝试从表中检索数据，并将多行合并到一个列中，而不重复任何信息。

I'm trying to retrieve data from tables and combine multiple rows into a single column, without repeating any information.

我有以下表：profile，qualification ，项目。

I have the following tables: profile, qualification, projects.

Profile

pro_id  surname       firstname   
------  -------       ----------
   1    John          James           
   2    King          Fred        
   3    Luxury-Yachts Raymond

Qualification

pro_id  Degree   School               Year
------  ------   ------               -----
   1    MBA      Wharton university   2002        
   1    LLB      Yale University      2001    
   2    BSc      Covington University 1998
   2    BEd      Kellog University    1995

Projects

pro_id  Title                         Year
------  ------                        ------   
   1    Social Networking             2003        
   1    Excavation of aquatic debris  2007    
   2    Design of solar radios        1992
   2    Development of expert systems 2011

我想检索每个人的所有信息，每个人在结果中只出现一次。资格和项目信息应该在各自的列中（一列用于资格，另一列用于项目），用逗号分隔。例如，上述示例数据的结果应为：

I want to retrieve the all of the information for each person, with each person appearing only once in the result. The info on qualifications and projects should each be in their own column (one column for qualifications, another for projects), separated by commas. For example, the results for the above sample data should be:

1  John James             MBA Wharton university 2002, LLB Yale University 2001                     Social Networking 2003, Excavation of aquatic debris 2007, Design of Solar panels 2008
2  King Fred              BSc Covington University 1998, BEd Kellog University 1995, Msc MIT 2011   Design of solar radios 1992, Development of expert systems 2011
3  Raymond Luxury-Yachts

目前，我有以下查询：

SELECT pro_id,
       surname,
       firstname,
       group_concat(degree,school,year) AS qual,
       concat(Title,year) AS work
FROM profile,
       LEFT JOIN qualification
           ON qualification.pro_id = profile.pro_id 
       JOIN projects
           ON projects.pro_id = profile.pro_id 
GROUP BY pro_id

对于示例数据，此查询将导致：

For the sample data, this query results in:

1  John James MBA  Wharton university 2002,  Social Networking 2003 
1  John James LLB  Yale University 2001, Excavation of aquatic debris 2007
1  John James MBA  Wharton university 2002,  Social Networking 2003, Excavation of aquatic debris 2007
etc

注意：Raymond Luxury-Yachts不在当前结果中。

Note: Raymond Luxury-Yachts isn't present in the current result.

我不想要重复的结果记录。如果surname在qualification和projects表中没有任何条目，我希望查询返回该名称，并在qualification和projects表中显示一个空字段，而不是完全省略它们。

I don't want duplicate result records. Also if the surname does not have any entry in the qualification and projects table, I want the query to return the name and display an empty field in the qualification and projects table instead of omitting them altogether.

推荐答案

我想你很接近你对group_concat的想法。然而，使用可能的No值（因此留下null），可能会导致问题。我会有每个二级表pre-concatinated人的ID，并加入到THAT结果。消除null的问题

I think you are close on your thoughts of group_concat. However, with possible No values (thus leaving nulls), can cause problems. I would have each secondary table pre-concatinated by person's ID and join to THAT result. Eliminates the problem of nulls

SELECT
      p.pro_id,
      p.surname,
      p.firstname,
      PreQConcat.UserQual,
      PrePJConcat.UserWork
   FROM 
      profile p
         LEFT JOIN 
            ( select q.pro_id,
                     group_concat( q.degree, q.school, q.year) AS UserQual
                 from
                     qualification q
                 group by
                     q.pro_id ) PreQConcat
            ON p.Pro_ID = PreQConcat.pro_id

         LEFT JOIN 
            ( select pj.pro_id,
                     concat(pj.Title, pj.year) AS UserWork
                 from
                     projects pj
                 group by
                     pj.pro_id ) PrePJConcat
            ON p.Pro_ID = PrePJConcat.pro_id

你正在经历所有的人，想要所有他们各自的元素（当他们存在时）分组，所以为什么组可能性不存在。让JOINED查询每个运行一次，完成一个结果，只有那些拥有数据的人分组，然后再加入到原来的个人资料。

You are going through all people anyhow, and want all their respective elements (when they exist) grouped, so why group on a possibility it doesn't exist. Let the JOINED queries run once each, complete with a single result grouped by only those people it had data for, then join back to the original profile person.

这篇关于将多个表中的数据合并为单行，同时连接一些值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！