mysql Query SQL display score

2cmtqfgy 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(118)

I need some help. I want to display All scores from students, If the score has been input to db, it will display the score, if not it will display null value.
Database structure like this :

students

nim
name
class_year (like : 2020, 2021, etc)
krs
code_krs
nim
academic_year (like : 20201, 20202, etc)
total_sks
krs_detail
id
code_krs
course_code
sks
courses
course_code
course_name
sks
scores
academic_year (like : 20201, 20202, etc)
course_code
nim
score

I try query like this : `

SELECT students.nim, students.name, krs_detail.course_code, courses.course_name, courses.sks, scores.score
FROM students
JOIN scores ON scores.nim = students.nim
RIGHT JOIN courses ON courses.course_code = scores.course
JOIN krs_detail ON krs_detail.course_code = courses.course_code
WHERE krs_detail.code_krs = '1411503293-20222';

Data displayed is correct, with nim & name student : enter image description here
But, if i change krs_detail.code_krs with other data, example 2161206003-20211, data displayed is wrong :
enter image description here
All data score should be null/empty, because score has not been insert to db, nim displayed : 2161206003, name : Farhan.
Thanks for your help

mysql

来源：https://stackoverflow.com/questions/74814304/query-sql-display-score

1条答案

按热度按时间

iibxawm41#

My interpretation is that

There are two master tables: students and courses .
A student can take a number of courses in an academic year, which is recorded in krs and krs_detail
A student will receive a score for the course he takes in the academic year, which is recorded in scores table.

If so, the data model looks like below:

The score report needs to display

All the courses taken by a student, and
All the scores received by a student
If a score is not yet available, display the course code , course name and sks .

If the above is correct, the granularity of the dataset is ( nim , academic_year , course_code )
We can

First, generate the result of all the courses taken by students
Second, left outer join from the above to scores to retrieve score .

select s.nim,
       s.name,
       k.academic_year,
       d.course_code,
       c.course_name,
       c.sks,
       sc.score
  from students s
  join krs k
    on s.nim = k.nim
  join krs_detail d
    on k.code_krs = d.code_krs
  join courses c
    on d.course_code = c.course_code
  left
  join scores sc
    on s.nim = sc.nim
   and k.academic_year = sc.academic_year
   and d.course_code = sc.course_code
 order by s.nim, d.course_code;

An example of result:

nim|name|academic_year|course_code|course_name                |sks|score|
---+----+-------------+-----------+---------------------------+---+-----+
101|adam|         2022|        401|An Introduction to Computer|  3|   85|
101|adam|         2022|        402|Programming Language       |  3|     |
201|eve |         2022|        401|An Introduction to Computer|  3|   90|
201|eve |         2022|        402|Programming Language       |  3|     |

赞(0）回复(0）举报 2022-12-22

我来回答

mysql Query SQL display score

1条答案

相关问题

热门标签

最新问答