mysql - GROUP_CONCAT 不合并字段

Question

我在使用 GROUP_CONTACT 时遇到语法问题。

使用如下所示的 sql 语句:

SELECT 

     s.school_code
    , s.school_name
    ,st.subject
    ,sg.subgroup
    ,GROUP_CONCAT(IF(r.year=2012,a.proficiency_index,NULL)) AS pi_2012
    ,GROUP_CONCAT(IF(r.year=2013,a.proficiency_index,NULL)) AS pi_2013

FROM 
    ayp_data a
        INNER JOIN 
            report_year r ON
                a.report_year_id = r.id
        INNER JOIN
            school s ON
                a.school_code_id = s.id
        INNER JOIN
            sub_group sg ON
                a.subgroup_id = sg.id
        INNER JOIN
            `subject` st ON
                a.subject_id = st.id

GROUP BY
      report_year_id,
      s.school_code
    , s.school_name
    ,st.subject
    ,sg.subgroup
HAVING 
s.school_name = 'Moody Elementary School' AND 
`subject` = 'Mathematics' AND 
`subgroup` = 'All Students'

我得到这样的结果:

SCHOOL_CODE     SCHOOL_NAME     SUBJECT     SUBGROUP    PI_2012     PI_2013
0065    Moody Elementary School     Mathematics     All Students    9.640000343322754   (null)
0065    Moody Elementary School     Mathematics     All Students    (null)  10.920000076293945

我想将两行合并为一行，并将非空字段值 PI_2012 和 PI_2013 放在同一行。

我以为我可以用 GROUP_CONTACT 做到这一点；但它并没有像我想象的那样。

如何使用 GROUP_CONCAT 合并这些字段？

或者，是否有更聪明的方法来做到这一点？

我有完整的架构和查询 here on SQL Fiddle .

Answer 1

如果您不希望单独的年份出现在单独的行中，那么您需要从 GROUP BY 子句中删除 report_year_id。也就是说——你需要改变这个:

GROUP BY
      report_year_id,
      s.school_code
    , s.school_name
    ,st.subject
    ,sg.subgroup

为此:

GROUP BY
      s.school_code
    , s.school_name
    ,st.subject
    ,sg.subgroup