我有三张桌子。

课程
课程
名称

标记
标识
学生
课程
标记

学生
学生
名称
如何交叉查询这样的结果?
 结果
表头

学生编号课程1课程2 ...课程N
id1 mark1 mark2 ... markN
id2 mark1 mark2 ... markN
id3 mark1 mark2 ... markN

最佳答案

最好的,我可以考虑是否要使用group_concat函数。
因为列数的变化只是维护的噩梦。
您可以将coursegrades字段中的值除以exploding php中的列表或客户端中使用的任何逗号分隔符。

  /*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

关于mysql - 交叉表查询,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/6129624/

10-13 01:24