在SQL中使用CASE函数

Question

我有一张这样的桌子：

2   Savita  EC1 50
2   Savita  EC2 55
2   Savita  EC3 28
2   Savita  EC4 30
2   Savita  EC5 60
3   Abhi    EC1 40
3   Abhi    EC2 38
3   Abhi    EC3 35
3   Abhi    EC4 45
3   Abhi    EC5 37
4   Priya   EC1 60
4   Priya   EC2 49
4   Priya   EC3 26
4   Priya   EC4 29
4   Priya   EC5 44
5   Shanthi EC1 70
5   Shanthi EC2 19
5   Shanthi EC3 45
5   Shanthi EC4 44
5   Shanthi EC5 50
6   Harish  EC1 60
6   Harish  EC2 64
6   Harish  EC3 26
6   Harish  EC4 28
6   Harish  EC5 29

我想加上宽限期分数，条件是每个学生的总宽限期分数最多为6分，如果候选人在EC1中的28分和EC2中的27分两个科目中不及格，那么在加上宽限期EC1=30和EC2=30后，他是通过的。如果他在EC1中有25分，在EC2中有28分，那么需要的宽限期分数是5 2=7。因此，他是不及格的，没有宽限期分数，宽限期分数可以添加到最多两个科目。如果他在两个科目中不及格，那么他是不及格的，没有宽限期分数。我有一个程序像

create procedure SP_student3
as
begin
select FstudentName,EC1,EC2,EC3,EC4,EC5,TOTALMARKS, CASE 
WHEN EC1<30 THEN 'FAIL'
WHEN EC2<30 THEN 'FAIL'
WHEN EC3<30 THEN 'FAIL'
WHEN EC4<30 THEN 'FAIL'
WHEN EC5<30 THEN 'FAIL'
ELSE 'PASS' END AS RESULT FROM(select FstudentName,EC1,EC2,EC3,EC4,EC5,TOTALMARKS=EC1+EC2+EC3+EC4+EC5 FROM Student   PIVOT(SUM(FMarks) for Fsubject in ([EC1],[EC2],[EC3],[EC4],[EC5],TOTALMARKS]))ASPIVOTTABLE )B end

它给出了

Abhi    40  38  35  45  37  195 PASS
Harish  60  64  26  28  29  207 FAIL
Priya   60  49  26  29  44  208 FAIL
Savita  50  55  28  30  60  223 FAIL
Shanthi 70  19  45  44  50  228 FAIL

Answer 1

我对这个问题采取了稍微不同的方法。如果您向表中添加两列-“GraceMarks”（int）和Pass（bit），那么您可以通过一系列顺序和逻辑更新查询以及最后的简单选择来完成此操作。我把这个放在一个叫做“test”的表格里，但你可以把这个改成“student”。

-- Find if students passed or failed the year

-- Assume students are all intitally flagged to Pass, and gracemarks are zero.
 update test SET Pass=1, GraceMarks=0

-- First, any mark <=23 is a fail
update test SET Pass=0 where Mark<=23 

-- if a student failed one subject, they fail them all
update test SET Pass=0 where StudentID in (
    SELECT      StudentID 
    from        dbo.test 
    where       Pass=0)

-- Next work out how many grace marks would be needed to pass each subject
update test SET GraceMarks=30-Mark where Mark<30 and Pass=1

-- If a student used more that a total of 6 grace marks, they failed too
update test SET Pass=0 where StudentID in (
    SELECT     StudentID 
    FROM         dbo.Test
    GROUP BY StudentID
    HAVING      (SUM(GraceMarks) > 6))

-- If they used grace marks in 3 or more subjects ... fail
update test SET Pass=0 where StudentID in (
    SELECT     StudentID
    FROM         dbo.Test
    WHERE     (GraceMarks > 0)
    GROUP BY StudentID
    HAVING      (COUNT(GraceMarks) > 2))    

-- Now show results
select      StudentID, StudentName,
            sum(case course when 'EC1' then mark end) as EC1,
            sum(case course when 'EC2' then mark end) as EC2,
            sum(case course when 'EC3' then mark end) as EC3,
            sum(case course when 'EC4' then mark end) as EC4,
            sum(case course when 'EC5' then mark end) as EC5,
            SUM(mark) as totalMark,
            CASE Pass WHEN 0 THEN 'Fail' ELSE 'Pass' END AS YearPassorFail

from        dbo.Test 
group by    StudentID, StudentName, CASE Pass WHEN 0 THEN 'Fail' ELSE 'Pass' END
order by        StudentName

此代码的输出是此表 --

3   Abhi    40  38  35  45  37  195 Pass 
6   Harish  60  64  26  28  29  207 Fail
4   Priya   60  49  26  29  44  208 Pass
2   Savita  50  55  28  30  60  223 Pass
5   Shanthi 70  19  45  44  50  228 Fail

您可以添加一个名为“原因”的附加列，并更改上面的查询，以包括它们失败的原因（Harish因为3个主题

此方法的优点是，您可以查看每个步骤中发生的情况，并且具有使用的通过/失败和宽限标记的记录。然后，您可以编写查询来查找诸如 - 使用了多少个宽限度标记之类的东西？如果恩典标记只在3岁时被允许，谁会失败？等。我个人也喜欢许多简单的步骤，而不是一个巨大的复杂步骤，但那可能只是我。

Answer 2

如果我的假设是正确的，那么你正在寻找：

SELECT A.StudentName, EC1,EC2,EC3,EC4,EC5,Total,
  case when failures > 6 or subjects > 2 then 'Failure'
       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 30 - Marks else 0 end) as failures,
      sum(case when Marks <  30 then 1 else 0 end) as subjects,
      sum(marks) as total
  from
      student
  group by
      studentname
) B
where 
    A.StudentName = B.StudentName

这非常接近您上一个问题中的答案

小提琴

编辑:增加了对2名受试者的检查，尽管测试数据不包含任何类似的情况。