SQL语句计算一张表中两数值类型属性百分比，并根该百分比进行判定是否优秀、合格、不合格，最后返回每条记录百分比和判定结果

1.需求说明：根据userId(用户ID) 和 subjectId(学科ID) 在试卷答案表中查询已审阅的符合条件的记录集合，要求计算用户得分（user_score）与试卷总分(paper_score)的百分比, 并根据该百分比判定是否优秀、合格、不合格，且返回字段除了基本字段外，也需返回每条记录的百分比即precent 和 判定结果即isReach。

（提示：判定规则为大于等于90%为优秀，大于等于80%为合格，其余为不合格）

2.SQL语句写法一（未优化）：

a.SQLyog中执行写法如下：

#user_id代表用户id, subject_id代表学科id
#user_score代表用户最终得分, paper_score代表试卷总分
#status代表该试卷答案是否批阅，1是未批阅 2是已批阅
SELECT user_id, subject_id, paper_name, user_score, paper_score, create_time, `status`, CONCAT(ROUND(user_score / paper_score * 100,2),'%') AS precent,
(CASE WHEN ROUND(user_score / paper_score * 100, 2) >= 90 THEN '优秀' WHEN ROUND(user_score / paper_score * 100, 2) >=80 THEN '合格' ELSE '不合格' END) AS isreach
FROM paper_answer WHERE user_id= 1 AND subject_id = 4 AND `status` = 2;

b.Mybatis的xml文件中写法如下：

<!--user_id代表用户id, subject_id代表学科id-->
<!--user_score代表用户最终得分, paper_score代表试卷总分-->
<!--status代表该试卷答案是否批阅，1是未批阅 2是已批阅-->
<!-- 根据用户Id和学科Id查询考试答案列表 -->
<!-- PaperAnswerVo是根据返回结果创建的虚拟实体类 -->
<select id="selectAnswerList" resultType="com.paper.domain.PaperAnswerVo" parameterType="com.paper.domain.PaperAnswerVo">
    SELECT user_id, subject_id, paper_name, user_score, paper_score, create_time, `status`,                             
    CONCAT(ROUND(user_score / paper_score * 100, 2), '%') AS precent,
    (CASE WHEN ROUND(user_score / paper_score * 100, 2) &gt;= 90 THEN '优秀' WHEN     
    ROUND(user_score / paper_score * 100, 2) &gt;= THEN '合格' ELSE '不合格' END) AS isreach
    FROM paper_answer WHERE user_id= #{userId} AND subject_id = #{subjectId} AND `status` = 
    #{status}
</select>

3.SQL语句写法二（优化）：

a.SQLyog中执行写法如下：

#user_id代表用户id, subject_id代表学科id
#user_score代表用户最终得分, paper_score代表试卷总分
#status代表该试卷答案是否批阅，1是未批阅 2是已批阅
SELECT  create_user AS user_id, subject_id, paper_name, user_score, paper_score, create_time, `status`, CONCAT(pt, '%') AS precent,
(CASE WHEN pt >= 90 THEN '优秀'WHEN pt >=80 THEN '合格' ELSE '不合格' END) AS isreach  
FROM (SELECT *, ROUND(user_score / paper_score * 100,2) AS pt FROM t_exam_paper_answer) a
WHERE create_user = 1  AND subject_id = 4 AND `status` = 2;

b.Mybatis的xml文件中写法如下：

<!--user_id代表用户id, subject_id代表学科id-->
<!--user_score代表用户最终得分, paper_score代表试卷总分-->
<!--status代表该试卷答案是否批阅，1是未批阅 2是已批阅-->
<!-- 根据用户Id和学科Id查询考试答案列表,并根据用户得分降序排列 -->
<!-- PaperAnswerVo是根据返回结果创建的虚拟实体类 -->
<select id="selectAnswerList"  resultType="com.paper.domain.PaperAnswerVo" parameterType="com.paper.domain.PaperAnswerVo">
    SELECT user_id, subject_id, user_score, paper_name，paper_score, create_time, `status`, 
    CONCAT(dp, '%') AS precent,
    (CASE WHEN dp &gt;= 90 THEN '优秀' WHEN dp &gt;= 80 THEN '合格'  ELSE '不合格' END) AS         
    isReach FROM (SELECT *, ROUND(user_score / paper_score * 100, 2) AS dp FROM         
    paper_answer) a
    <where>
      and user_id= #{userId} and status = 2
      <if test="subjectId != null">
	      and subject_id = #{subjectId}
      </if> order by user_score desc
    </where>  
</select>

4.SQL语句执行效果图如下：

5.欢迎大家指正，谢谢！