使用唯一记录作为表头

Question

null

_______________________________________________________________________________________________________________________________________________________________________________________________________
__student3______95_____92_____97__
__student4______97_________91___

学生姓名来自学生表
科目1,科目2,科目来自科目表的唯一记录
,然后
成绩表

是可能的还是我的数据库模式设计不好？

以下是表记录

Student table
|student_id | student_name|
|     1     |   student1  |
|     2     |   student2  |

Subject table
|subject_id | subject_name|
|     1     |   subject1  |
|     2     |   subject2  |
|     3     |   subject3  | 

Grade table
| grade_id | grade | 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      |

Answer 1

一般的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;