mysql - 更改当前的MySql查询以根据ID合并结果

我下面的查询得到了所有报名参加比赛的评委。这个
in子句中的子查询是因为事件可以是一天或多天，并且
评委可以报名参加一个或多个。这很好，但如果是两天的活动
注册这两个，他们是2行返回给用户。我想把它们组合起来，这样就可以显示它们的信息和两个活动日。我该怎么办？

select concat(ej.FirstName, ' ', ej.LastName) as judge, ej.email, ej.phone, ed.EventDate
from EventJudges ej
inner join EventJudgeXref ejx on ej.Id = ejx.EventJudgeId
inner join EventDates ed on ejx.EventDateId = ed.Id
where ejx.EventDateId in(SELECT Id FROM EventDates WHERE EventId = 1)
order by ej.FirstName asc, ej.LastName asc

eventjudgeref表包含一个JudgeId和一个供参考的EventDateId。

最佳答案

尝试使用group_concat：

select concat(ej.FirstName, ' ', ej.LastName) as judge,
    ej.email,
    ej.phone,
    group_concat(ed.EventDate) as EventDate
from EventJudges ej
inner join EventJudgeXref ejx on ej.Id = ejx.EventJudgeId
inner join EventDates ed on ejx.EventDateId = ed.Id
where ejx.EventDateId in(SELECT Id FROM EventDates WHERE EventId = 1)
group by ej.Id
order by ej.FirstName asc, ej.LastName asc

SQL Fiddle

关于mysql - 更改当前的MySql查询以根据ID合并结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/23620507/