需要SQL查询以满足以下要求

I have a table containing 5000 rows of students and 6 subjects in columns including student id and student name. Each student got a grade for each subject.Grades contain A1, A2, B1, B2 n up to E. I want to get the list of students who got A1 or A2 at least in 4 subjects out of six subjects. And I need an SQL query to get the list of such students Any help would be appreciated. Thank you

我有一个包含5000行学生和6个科目的表格,其中包括学生ID和学生姓名。每个学生都得到了每个科目的成绩.Grades包含A1,A2,B1,B2 n到E。我想得到六个科目中至少有4个科目获得A1或A2的学生名单。我需要一个SQL查询来获取这些学生的列表任何帮助将不胜感激。谢谢

1 个解决方案

#1

This should do it:

这应该这样做:

select * from (select [Student Id], [Student Name], (case when s1 in('A1','A2') then 1 else 0 end + 
case when s2 in('A1','A2') then 1 else 0 end +
case when s3 in('A1','A2') then 1 else 0 end +
case when s4 in('A1','A2') then 1 else 0 end +
case when s5 in('A1','A2') then 1 else 0 end +
case when s6 in('A1','A2') then 1 else 0 end ) as [A1A2 Subjects]

FROM [StudentMarks] ) A where [A1A2 Subjects] >=4

来自[StudentMarks])A [A1A2受试者]> = 4

Hope it helps.

希望能帮助到你。

#1