sql - 在数据透视查询中使用 if else block

Question

我有一张 table

StudentID StudentName Subject Marks
      1   Savita      EC1     50
      1   Savita      EC2     55
      1   Savita      EC3     45
      1   Savita      EC4     34
      1   Savita      EC5     23
      2   Rajesh      EC1     34
      2   Rajesh      EC2     56
      2   Rajesh      EC3     12
      2   Rajesh      EC4     45
      2   Rajesh      EC5     23
      3   Smita       EC1     76
      3   Smita       EC2     45
      3   Smita       EC3     67
      3   Smita       EC4     56
      3   Smita       EC5     76
      4   Rahul       EC1     66
      4   Rahul       EC2     34
      4   Rahul       EC3     22
      4   Rahul       EC4     18
      4   Rahul       EC5     33

我写了一个查询

SELECT StudentName, EC1,EC2,EC3,EC4,EC5,TotalMarks, case  
  when EC1<30 and ec2<30 then 'fail'
  when EC1<30 and EC3<30 then 'fail'
  when EC1<30 and EC4<30 then 'fail'
  when EC1<30 and EC5<30 then 'fail'
  when EC2<30 and EC3<30 then 'fail'
  when EC2<30 and EC4<30 then 'fail'
  when EC2<30 and EC5<30 then 'fail'
  when EC3<30 and EC4<30 then 'fail'
  when EC3<30 and EC5<30 then 'fail'
  when EC4<30 and EC5<30 then 'fail'
  else 'pass'
  end as Result
FROM (SELECT StudentName, EC1, EC2, EC3, EC4, EC5, TotalMarks=EC1+EC2+EC3+EC4+EC5
      FROM Student
      PIVOT(sum(Marks) for subject in([EC1],[EC2],[EC3],[EC4],[EC5],[TotalMarks]))as pivotTable) A

这给出了 2 个科目低于 30 分的学生的输出，即不及格或通过

Rahul   66  34  22  18  33  173 fail
Rajesh  34  56  12  45  23  170 fail
Savita  50  55  45  34  23  207 pass
Smita   76  45  67  56  76  320 pass

我想给每个小于30分的科目加7分，加7分后看到通过的学生不及格。例如-添加 7 分后 rajesh 记录应该是这样的

拉杰什 34 56 19 45 30 170 传球

Answer 1

也许这就是您正在寻找的东西:

SELECT A.StudentName, EC1,EC2,EC3,EC4,EC5,Total,
  case when fail2 >= 2 then 'Failure'
       when fail >= 2 then 'Near Pass'
       else 'Pass' end as Result
FROM
(  SELECT StudentName, EC1, EC2, EC3, EC4, EC5
      FROM Student
      PIVOT(sum(Marks) for subject in([EC1],[EC2],[EC3],[EC4],[EC5]))as pt) A,
(  select
      studentName,
      sum(case when Marks <  30 then 1 else 0 end) as fail,
      sum(case when Marks <  23 then 1 else 0 end) as fail2,
      sum(case when Marks >= 30 then 1 else 0 end) as pass,
      sum(marks) as total
  from
      student
  group by
      studentname
) B
where 
    A.StudentName = B.StudentName

我删除了所有失败组合的比较逻辑，并将其替换为原始表中的 sum + group by + case，这样您就可以确定每个学生的失败、接近通过和通过的计数必须单独列出所有案例。

您可以在 SQL Fiddle 中对此进行测试