在Oracle中会用到
percentile_cont(比例) WITHIN GROUP( ORDER BY to_number(分数) )
的写法,但在MySQL中却没有percentile_cont函数用法,所以只能根据函数思想使用排序及LIMIT改编算法(亲测,基本无误差)
Oracle
create function fn_get_grade(score float)
/**
** 五段分为法
** 大于84% 是A等级
** 小于等于84% 且 大于 63% 是B等级
** 小于等于63% 且 大于 37% 是C等级
** 小于等于37% 且 大于 16% 是D等级
** 小于等于16% 是E等级
**/
RETURN VARCHAR2 IS
RESULT VARCHAR2(10);
grade_a FLOAT;
grade_b FLOAT;
grade_c FLOAT;
grade_d FLOAT;
begin
SELECT percentile_cont(0.84) WITHIN GROUP( ORDER BY to_number(total_score) ) p INTO grade_a
FROM fb_unit_assessment_report r
;
SELECT percentile_cont(0.63) WITHIN GROUP( ORDER BY to_number(total_score) ) p INTO grade_b
FROM fb_unit_assessment_report r
;
SELECT percentile_cont(0.37) WITHIN GROUP( ORDER BY to_number(total_score) ) p INTO grade_c
FROM fb_unit_assessment_report r
;
SELECT percentile_cont(0.16) WITHIN GROUP( ORDER BY to_number(total_score) ) p INTO grade_d
FROM fb_unit_assessment_report r
;
IF score > grade_a THEN
RESULT := 'A';
ELSIF score <= grade_a AND score > grade_b THEN
RESULT := 'B';
ELSIF score <= grade_b AND score > grade_c THEN
RESULT := 'C';
ELSIF score <= grade_c AND score > grade_d THEN
RESULT := 'D';
ELSE
RESULT := 'E';
END IF;
return(Result);
end fn_get_grade;
/
MySQL
create function fn_get_grade(score float) returns VARCHAR(10)
/**
** 五段分为法
** 大于84% 是A等级
** 小于等于84% 且 大于 63% 是B等级
** 小于等于63% 且 大于 37% 是C等级
** 小于等于37% 且 大于 16% 是D等级
** 小于等于16% 是E等级
**/
begin
DECLARE RESULT VARCHAR(10);
DECLARE a INT ;
DECLARE b INT ;
DECLARE c INT ;
DECLARE d INT ;
DECLARE grade_a FLOAT;
DECLARE grade_b FLOAT;
DECLARE grade_c FLOAT;
DECLARE grade_d FLOAT;
SELECT CAST(ROUND(COUNT(total_score)*(1-0.84)+1)AS signed) INTO a FROM fb_unit_assessment_report r ;
SELECT total_score INTO grade_a FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT a,1;
SELECT CAST(ROUND(COUNT(total_score)*(1-0.63)+1)AS signed) INTO b FROM fb_unit_assessment_report r ;
SELECT total_score INTO grade_b FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT b,1;
SELECT CAST(ROUND(COUNT(total_score)*(1-0.37)+1)AS signed) INTO c FROM fb_unit_assessment_report r ;
SELECT total_score INTO grade_c FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT c,1;
SELECT CAST(ROUND(COUNT(total_score)*(1-0.16)+1)AS signed) INTO d FROM fb_unit_assessment_report r ;
SELECT total_score INTO grade_d FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT d,1;
IF score > grade_a THEN
set RESULT = 'A';
ELSEIF score <= grade_a AND score > grade_b THEN
set RESULT = 'B';
ELSEIF score <= grade_b AND score > grade_c THEN
set RESULT = 'C';
ELSEIF score <= grade_c AND score > grade_d THEN
set RESULT = 'D';
ELSE
set RESULT = 'E';
END IF;
return(RESULT);
end