FLOOR()

函数说明

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

函数语法

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

参数释义

示例

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)