MySQL一条SQL语句查询多个科目的成绩
SELECT pn.name,c.`cname`,
SUM(CASE WHEN ps.coid='1' THEN sname END )AS 'domain',
SUM(CASE WHEN ps.coid='2' THEN sname END )AS 'skill',
SUM(CASE WHEN ps.coid='3' THEN sname END )AS 'politics',
SUM(CASE WHEN ps.coid='4' THEN sname END )AS 'quality',
SUM(CASE WHEN ps.coid='5' THEN sname END )AS 'practice'
FROM pf_score ps INNER JOIN pf_student pn ON ps.`id`=pn.`id`
INNER JOIN pf_class c ON c.`cid`=pn.`cid`
GROUP BY pn.`name`,c.cname
详解
select pn.name(姓名)
sum(case when 科目的编号=1 then 成绩分数 end) as "重新命名的名字"
from pf_score(成绩表) ps
inner join pf_student(学生表) pn on pn.id =ps.id
group by pn.name
成绩表(pf_score)
学生表(pf_student)
科目表(pf_course)