我有这三张桌子
[student_id(pk), firstname, lastname]
[subject_id(pk), subject_title]
[score_id(pk), score, student_id(fk), subject_id(fk)]
有没有一种方法可以提出查询,以便以这种形式将其显示到表中:
_______________________________________
| 学生姓名| 主题1 | 主题2 | 主题3 |
-------------------------------------------------- ------------
| __student1 ___ | ___ 98 ____ | ___ 92 ___ | ___ 97 ___ |
| __student2 ___ | ___ 93 ____ | ___ 91 ___ | ___ 95 ___ |
| __student3 ___ | ___ 95 ____ | ___ 92 ___ | ___ 97 ___ ||
| __student4 ___ | ___ 97 ____ | ___ 95 ___ | ___ 91 ___ ||
学生姓名来自“学生”表“
科目1”,“科目2”,“科目”来自科目表的唯一记录,
然后
来自“成绩”表
有可能还是我的数据库架构设计不好?
这是表格记录
学生表 | student_id | 学生名| | 1 | 学生1 | | 2 | 学生2 |
主题表 | subject_id | subject_name | | 1 | subject1 | | 2 | subject2 | | 3 | subject3 |
成绩表 | grade_id | 等级| subject_id | student_id | | 1 | 87 | 1 | 1 | | 2 | 87 | 2 | 1 | | 3 | 87 | 3 | 1 | | 4 | 87 | 1 | 2 | | 5 | 87 | 2 | 2 | | 6 | 87 | 3 | 2 |
通用的SQL方法是使用条件聚合:
select s.studentName,
max(case when s.subjectName = 'subject1' then g.grade end) as Subject1,
max(case when s.subjectName = 'subject2' then g.grade end) as Subject2,
max(case when s.subjectName = 'subject3' then g.grade end) as Subject3
from (students s join
grades g
on s.student_id = g.student_id
) join
subjects su
on g.subject_id = su.subject_id
group by s.studentid, s.studentName;
一些数据库也支持pivot
执行此操作的语法。
编辑:
访问查询是:
select s.studentName,
max(iif(s.subjectName = 'subject1', grade, NULL)) as Subject1,
max(iif(s.subjectName = 'subject2', grade, NULL)) as Subject2,
max(iif(s.subjectName = 'subject3', grade, NULL)) as Subject3
from students s inner join
grades g
on s.student_id = g.student_id inner join
subjects su
on g.subject_id = su.subject_id
group by s.studentid, s.studentName;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句