mysql - Student's report

Question

I need a student's mark report.

I have the following tables: 
- user table with: user_id, first name, last name, course_id
- a course table with: course_id, name
- a subject table with: subject_id, course_id, name
- a marks table with: mark, subject_id, course_id, user_id

I need to show all students from a selected course with all their marks for all their subjects.

For example: course_id = 18

Last Name | Name | Math | Geography | Science | Physics
Doe       | John |  8   |     7     |    4    |    7
Doe       | Jane |  5   |     8     |    4    |    6

and so on until the last student ordered alphabetically using last name.

Is possible to achieve using SQL? No need php nor anything.

Thanks in advance!

标签： mysqlsqlmariadb

Answer 1

选择 u.last_name , u.first_name , s.name,m.mark

来自用户 u ，主题 s ，标记 m

其中 u.user_id = m.user_id and s.subject_id = m.subject_id and s.course_id = m.course_id and s.course_id = 18 order by u.last_name asc

此查询仅适用于课程 ID 18，但您可以拥有所有课程 ID。

此查询不会产生您提到的格式的结果，但对于学生注册的每个科目，它将有一个单独的行。

希望这可能会有所帮助。