SQL查询学生成绩 [英] sql query for student result

问题描述

hai朋友，
我有两个桌子
1.学生成绩
字段:sti stname regno subjec1 subjec2 subjec3 subjec4
1 xxxx 125 a b a rsa
2.subject:
字段:subjectcode主题名称
subjec1 c
subjec2 c ++
subjec3 java
subjec4 .net

这两个表之间没有关系.
但是我需要如下结果表
主题代码主题名称成绩结果
subjec1 c通行证
subjec2 c ++ b pass
subjec3 java通行证
subjec4 .net rsa失败

hai friends,
i have two table
1.studentgrade
fields:stid stname regno subjec1 subjec2 subjec3 subjec4
1 xxxx 125 a b a rsa
2.subject:
fields: subjectcode subjecname
subjec1 c
subjec2 c++
subjec3 java
subjec4 .net

There is no relationship between these two tables.
But i need the result table as follows
subjectcode subjectname grade result
subjec1 c a pass
subjec2 c++ b pass
subjec3 java a pass
subjec4 .net rsa fail

what is the query for getting this result?.

推荐答案

试试这个查询

Hi,
Try this query

SELECT SU.SubjectCode, SU.SubjectName,
 CASE WHEN SU.SubjectCode='Subject1'
         THEN (SELECT Distinct Subject1 FROM StudentGrade)
 CASE WHEN SU.SubjectCode='Subject2'
         THEN (SELECT Distinct Subject2 FROM StudentGrade)
 CASE WHEN SU.SubjectCode='Subject3'
      THEN (SELECT Distinct Subject3 FROM StudentGrade)
 CASE WHEN SU.SubjectCode='Subject4'
      THEN (SELECT Distinct Subject4 FROM StudentGrade)
 ELSE NULL END AS 'Grade',
 CASE WHEN Grade IN ('a','b') THEN 'Pass' ELSE 'Fail' END AS 'Result'
 FROM Subject SU

希望它能起作用

Hope it works

这篇关于SQL查询学生成绩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！