mysql 五舍六入_四舍六入五成双（适用于MYSQL）（最大支持小数点第9位）

ZRound

CREATE FUNCTION ZRound(num DECIMAL(38,14) ,i INT)

RETURNS varchar(20)

BEGIN

/*原数据转换成字符类型*/

DECLARE str VARCHAR (100);

/*保留小数位之后的数字 --如：1.5401 保留一位时 str2='401' */

DECLARE str2 VARCHAR (100);

/*保留的最后一位小数 --如：1.54 保留一位时 str3='5' */

DECLARE str3 VARCHAR (2);

SET str = CAST(num AS CHAR);

/*若为整数，则返回原值*/

IF LOCATE('.' ,str) = 0 THEN

RETURN str;

END IF;

/*保留小数位之后的数字*/

SET str2 = REVERSE(

SUBSTR(REVERSE(str), 1, LOCATE('.', REVERSE(str)) - 1 - i )

);

/*保留的最后一位小数 ,需注意若保留小数位数为0时，小数点的截取问题*/

IF i = 0 THEN

SET str3 = SUBSTR(str, LOCATE('.' ,str) - 1, 1);

ELSE

SET str3 = SUBSTR(str, LOCATE('.' ,str) + i, 1);

END IF;

/*符合五成双的条件*/

IF SUBSTR(str2, 1, 1) != 0 AND SUBSTR(str2, 1, 1) % 5 = 0 THEN

/*判断5后面是否还存在不为0的数字*/

IF LENGTH(str2) >= 2 AND SUBSTR(str2, 2, LENGTH(str2) - 1) > 0 THEN

/*若存在，则进一(注意小数点的截取问题)*/

SET str = ZRoundSub(str,i,TRUE);

/*不存在，另判断需保留小数最后一位的奇偶数，奇数进一，偶数舍五*/

ELSE

/*偶数舍五 ,需注意小数点的截取问题*/

IF str3 % 2 = 0 THEN

SET str = ZRoundSub(str,i,FALSE);

/*奇数进一 ,需注意小数点的截取问题*/

ELSE

SET str = ZRoundSub(str,i,TRUE);

END IF;

END IF;

/*四舍六入*/

ELSE

SET str = CAST(Round(str ,i) AS CHAR);

END IF;

RETURN str;

END;

ZRoundSub

CREATE FUNCTION ZRoundSub(str VARCHAR(50) ,i INT,flag INT)

RETURNS varchar(20)

BEGIN

IF flag THEN -- 入

CASE i

WHEN 0 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) - 1 + i) AS DECIMAL(38,0)) + 1.0 / POWER(10 ,i) AS DECIMAL(38,0));

WHEN 1 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,1)) + 1.0 / POWER(10,i) AS DECIMAL(38,1));

WHEN 2 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,2)) + 1.0 / POWER(10,i) AS DECIMAL(38,2));

WHEN 3 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,3)) + 1.0 / POWER(10,i) AS DECIMAL(38,3));

WHEN 4 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,4)) + 1.0 / POWER(10,i) AS DECIMAL(38,4));

WHEN 5 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,5)) + 1.0 / POWER(10,i) AS DECIMAL(38,5));

WHEN 6 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,6)) + 1.0 / POWER(10,i) AS DECIMAL(38,6));

WHEN 7 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,7)) + 1.0 / POWER(10,i) AS DECIMAL(38,7));

WHEN 8 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,8)) + 1.0 / POWER(10,i) AS DECIMAL(38,8));

WHEN 9 THEN SET str = CAST(CAST(SUBSTR(str,1,LOCATE('.' ,str) + i) AS DECIMAL(38,9)) + 1.0 / POWER(10,i) AS DECIMAL(38,9));

END CASE;

ELSE-- 舍

IF i = 0 THEN

SET str = SUBSTR(str,1,LOCATE('.' ,str) - 1 + i);

ELSE

SET str = SUBSTR(str,1,LOCATE('.' ,str) + i);

END IF;

END IF;

RETURN str;

END;

调用函数

SELECT ZRound(11.5,0);

SELECT ZRound(11.15,1);