mysql - 跨表查询

Question

我有三张 table 。

类(class)
idcourse
姓名

标记
idmark
学生
idcourse
标记

学生
学生
姓名

如何交叉查询得到这样的结果？
结果
表格标题

studentid   course1 course2 ... courseN
id1         mark1   mark2   ... markN
id2         mark1   mark2   ... markN
id3         mark1   mark2   ... markN

Answer 1

我能想到的最好方法是使用group_concat函数。
因为可变数量的列只是维护的噩梦。
您可以通过在 php 中分解列表或客户端中的任何逗号分隔符来将 coursegrades 字段中的值分开。

  /*First make a list of coursenames for the header.*/
  SELECT 'studentid' as idstudent
    , 'studentname' as studentname
    ,  GROUP_CONCAT(course.name ORDER BY couselist.idcourse) as coursegrades
  FROM course
  INNER JOIN (SELECT DISTINCT idcourse FROM mark) courselist
    ON (courselist.idcourse = course.idcourse)

UNION ALL 

  /*UNION ALL this with a list of grades per course*/
  SELECT idstudent
    , student.name as studentname
    , GROUP_CONCAT(IFNULL(mark.mark,'') ORDER BY courselist.idcourse) as coursegrades
  FROM course
  INNER JOIN (SELECT DISTINCT idcourse FROM mark) courselist
    ON (courselist.idcourse = course.idcourse)
  LEFT JOIN mark ON (mark.idcourse = courselist.idcourse)
  INNER JOIN student ON (student.idstudent = mark.idstudent)
  GROUP BY student