FLOOR()

函数说明

FLOOR() 函数返回不大于某个数字的相应数位的数。

函数语法

  1. > FLOOR(number, decimals)
  2. > FLOOR(number)

参数释义

参数说明
number必要参数，任何当前支持的数值数据
decimals可选参数，代表小数点后的位数。默认值为 0，代表四舍五入为整数，当为负数时四舍五入到小数点前的数位。

示例

  1. drop table if exists t1;
  2. create table t1(a int ,b float);
  3. insert into t1 values(1,0.5);
  4. insert into t1 values(2,0.499);
  5. insert into t1 values(3,0.501);
  6. insert into t1 values(4,20.5);
  7. insert into t1 values(5,20.499);
  8. insert into t1 values(6,13.500);
  9. insert into t1 values(7,-0.500);
  10. insert into t1 values(8,-0.499);
  11. insert into t1 values(9,-0.501);
  12. insert into t1 values(10,-20.499);
  13. insert into t1 values(11,-20.500);
  14. insert into t1 values(12,-13.500);
  16. mysql> select a,floor(b) from t1;
  17. +------+----------+
  18. | a    | floor(b) |
  19. +------+----------+
  20. |    1 |        0 |
  21. |    2 |        0 |
  22. |    3 |        0 |
  23. |    4 |       20 |
  24. |    5 |       20 |
  25. |    6 |       13 |
  26. |    7 |       -1 |
  27. |    8 |       -1 |
  28. |    9 |       -1 |
  29. |   10 |      -21 |
  30. |   11 |      -21 |
  31. |   12 |      -14 |
  32. +------+----------+
  33. 12 rows in set (0.01 sec)
  35. mysql> select sum(floor(b)) from t1;
  36. +---------------+
  37. | sum(floor(b)) |
  38. +---------------+
  39. |            -6 |
  40. +---------------+
  41. 1 row in set (0.00 sec)
  43. mysql> select a,sum(floor(b)) from t1 group by a order by a;
  44. +------+---------------+
  45. | a    | sum(floor(b)) |
  46. +------+---------------+
  47. |    1 |             0 |
  48. |    2 |             0 |
  49. |    3 |             0 |
  50. |    4 |            20 |
  51. |    5 |            20 |
  52. |    6 |            13 |
  53. |    7 |            -1 |
  54. |    8 |            -1 |
  55. |    9 |            -1 |
  56. |   10 |           -21 |
  57. |   11 |           -21 |
  58. |   12 |           -14 |
  59. +------+---------------+
  60. 12 rows in set (0.00 sec)