我有三张桌子。
课程
课程
名称
标记
标识
学生
课程
标记
学生
学生
名称
如何交叉查询这样的结果?
结果
表头
学生编号课程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/