if 和 else if 在 mysql

Question

我正在使用表格 coursereg1sttbl 进行结果项目。讲师将只输入分数，并且根据总分更新成绩。使用 if 语句和 else 我能够让它工作。此代码适用于 php。我怎么想写一份在没有 php 的情况下在 sql 级别执行此操作的工作。因此，如果分数为 0 - 49，则等级将为 F，依此类推。我如何为此使用 sql。我的主要目标是根据条件更新表 coursereg1sttbl SET Grade = 等级值

<?php $sql = "SELECT * from  coursereg1sttbl WHERE matricno = '$matno'  AND registratioin_status ='1' ";

$query = $dbh -> prepare($sql);

$query->execute();

$results=$query->fetchAll(PDO::FETCH_OBJ);

$cnt=1;

if($query->rowCount() > 0)

{

foreach($results as $result)

{       

  //  Pharmacy Grading

  if($dptcode == 119 ){

      if($result->total_score >= 0 && $result->total_score <=49) $grade1='F';

      elseif($result->total_score >=50 && $result->total_score <=54) $grade1='E';

      elseif($result->total_score >=55 && $result->total_score <=59) $grade1='D';

      elseif($result->total_score>=60 && $result->total_score <=64) $grade1='C';

      elseif($result->total_score >=65 && $result->total_score <=69) $grade1='B';

      elseif($result->total_score >=70 && $result->total_score <=100) $grade1='A';

      else

      {

         $grade1 ='NA'; 

      }

  }   ?>  ```

Answer 1

使用 SQL 执行此任务的一个好方法（有些人称之为直方图分桶）是CASE WHEN ... THEN ...在查询中使用表达式。这是 SQL 的做法if ... else if .... else if ... else （请注意：与 SQL 的其余部分一样，它是声明性的，而不是过程式的if else。你告诉 SQL你想要什么，而你告诉 Javascript 或 C++ 或任何编程语言该做什么。）此查询可能会为您完成。SELECT CASE WHEN total_score >= 0&nbsp; AND total_score <=&nbsp; 49 THEN 'F'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN total_score >= 50 AND total_score <=&nbsp; 54 THEN 'E'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN total_score >= 55 AND total_score <=&nbsp; 59 THEN 'D'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN total_score >= 60 AND total_score <=&nbsp; 64 THEN 'C'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN total_score >= 65 AND total_score <=&nbsp; 69 THEN 'B'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHEN total_score >= 70 AND total_score <= 100 THEN 'A'&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 'NA' END AS grade,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp;*&nbsp;&nbsp;&nbsp; FROM coursereg1sttbl&nbsp; WHERE matricno = '$matno'&nbsp; &nbsp;AND registratioin_status ='1';尽管如此，以这种方式编码似乎很冗长且容易出错。也许有人可以发明更好的方法？