再次卡住 - 需要向 MySQL 查询添加另一个 GROUP_CONCAT

Question

感谢大家对我第一次尝试的帮助，

我正在尝试将第二个 group_concat 添加到查询中以将数据返回到 VB.Net 数据网格。我想将 2 列的结果汇总到新列中，如下所示：

现有数据

Date     Sponsor    Match_no     Team      
--------------------------------------------
1-1-11     Nike        1         Tigers     
1-1-11     Nike        1         Bears
2-1-11     Crisco      2         Llamas
2-1-11     Crisco      2         Tigers 


 Date     Sponsor    Match_no     Tags    
    --------------------------------------------
    1-1-11     Nike        1         Away
    1-1-11     Nike        1         Rained out
    2-1-11     Crisco      2         Home
    2-1-11     Crisco      2         Injury

并使用 GROUP_CONCATs 汇总两列以获得类似的结果

 Date     Sponsor    Match_no     Teams_playing         Tags
    ----------------------------------------------------------------
    1-1-11    Nike         1          Tigers vs Bears       Away, Rained Out
    2-1-11    Crisco       2          Llamas vs Tigers      Home, injury

我在这里听取了某人的建议并在 Team_matches 和 Matches_tags 上创建了联接表现

在有 7 个表：

Dates       Sponsors       Match        Team         Tags   matches_tags  team_matches 
 -------------------------------------------------------------------------------------- 
Date_id     Sponsor_id     Match_id      Team_id    Tag_id     Match_id      Team_id
Date        Sponsor_name   Match_no     Team_name   Tag_name   Tag_id        Match_id 
             date_id        sponsor_id 

到目前为止我的查询是：

select d.date, s.sponsor_name, m.match_no,   
group_concat(t.team_name separator ' vs ') Teams_playing, 
group_concat(tg.tag_name separator ' , ') Comments
from matchs m 
inner join matches_teams mte on mte.match_id = m.match_id
inner join matches_tags mta on mta.match_id = m.match_id
inner join team t on t.team_id = mte.team_id
inner join tags tg on tg.tag_id = mta.tag_id
inner join sponsors s on s.sponsor_id = m.sponsor_id
inner join dates d on d.date_id = s.date_id 
group by m.match_id, d.date, s.sponsor_name, m.match_no, tg.tag_id

并返回结果：

date    sponsor      match_no        teams playing    comments
--------------------------------------------------------------------
1-1-11   Nike           1          Bears vs Tigers    Rained out , Rained out 
1-1-11   Nike           1          Bears vs Tigers    Cancelled , Cancelled
1-1-11   Nike           3          Earwigs vs Goombas Away , Away
2-1-11   Crisco         2          Tigers vs Llamas   Away , Away

这不是我之后的结果：

Answer 1

SELECT  d.date, s.sponsor_name, m.match_no,   
        GROUP_CONCAT(t.team_name separator ' vs ') Teams_playing, 
        (
        SELECT  GROUP_CONCAT(tg.tag_name SEPARATOR ', ')
        FROM    matches_tags mta
        JOIN    tags tg
        ON      tg.tag_id = mta.tag_id
        WHERE   mta.match_id = m.match_id
        ) AS comments
FROM    matchs m 
JOIN    matches_teams mte on mte.match_id = m.match_id
JOIN    team t on t.team_id = mte.team_id
JOIN    sponsors s on s.sponsor_id = m.sponsor_id
JOIN    dates d on d.date_id = s.date_id 
GROUP BY
        m.match_id