当前位置: 动力学知识库 > 问答 > 编程问答 >

MySQL: Combine multiple row values into single column?

问题描述:

Database: MySQL

Table: Teams

TeamID INT

TeamName VARCHAR(20)

Table: People

PeopleID INT

FirstNameID INT

LastNameID INT

Table: TeamMembers

TeamID INT

PeopleID INT

Table Example: Teams

TeamID TeamName

1 Team Xstream

2 Team INsanity

Table Example: People

PeopleID FirstNameID LastNameID

1 1351 453

2 5463 763

3 976 8762

4 87 784

5 187 465

6 761 566

7 376 2134

Table Example: TeamMembers

TeamID PeopleID

1 1

1 3

1 7

2 2

2 4

2 5

2 6

Desired Output:

TeamName TeamMembers

Team Xstream John Smith/Jane Doe/Daniel Davis

Team INsanity Sally Sue/Tom Thomas/Jack Jones/Harry Henderson

There will not be a set number of TeamMembers per team, so it's not like I could have three subqueries because there will only be three team members. I've lightly looked online, but I always get the best and most thorough answers here. Any ideas or pointers, please let me know. I honestly have no idea where to begin here. Thanks.

网友答案:

I am guessing that the names of the people are actually stored somewhere considering you are just showing ID numbers as names. But you will want to use both CONCAT() and GROUP_CONCAT() for this result. The first step, will join all of the tables and use the CONCAT() function:

select t.teamname,
 concat(p.FirstNameId, ' ', p.LastNameId) teamMembers
from teams t
left join teammembers m
  on t.teamid = m.teamid
left join people p
  on m.peopleid = p.peopleid;

See SQL Fiddle with Demo, which will produce the result:

|      TEAMNAME | TEAMMEMBERS |
-------------------------------
|  Team Xstream |    1351 453 |
|  Team Xstream |    976 8762 |
|  Team Xstream |    376 2134 |
| Team INsanity |    5463 763 |
| Team INsanity |      87 784 |
| Team INsanity |     187 465 |
| Team INsanity |     761 566 |

Once you have the data, then apply the GROUP_CONCAT() function and GROUP BY the teamname:

select t.teamname,
  group_concat(concat(p.FirstNameId, ' ', p.LastNameId) SEPARATOR '/') teamMembers
from teams t
left join teammembers m
  on t.teamid = m.teamid
left join people p
  on m.peopleid = p.peopleid
group by t.teamname;

See SQL Fiddle with Demo

results:

|      TEAMNAME |                     TEAMMEMBERS |
---------------------------------------------------
| Team INsanity | 761 566/87 784/187 465/5463 763 |
|  Team Xstream |      976 8762/376 2134/1351 453 |
网友答案:

You didn't provide information on the table where names are actually stored, but what you are looking for is GROUP_CONCAT function. This is how you would use it to show the first and last name id's. I will leave it to you to join the name table and replace the name id fields in the query with the actual name fields.

SELECT t.TeamName, GROUP_CONCAT(CONCAT(p.FirstNameID, ' ', p.LastNameID))
FROM Teams as t
INNER JOIN TeamMembers as tm on t.TeamID = tm.TeamID
INNER JOIN People as p on tm.PeopleID = p.PeopleID
GROUP BY t.TeamName

By the way, normalizing the names out into their own table and using nameID seems like probably a good example of over normalization. Why not just have name values in People table? This will save you adding a fourth table to your query (and possibly fifth table if first and last names are in different tables).

分享给朋友:
您可能感兴趣的文章:
随机阅读: