在SQLite中找到一个数的ciel和floor的最简洁的方法是什么?不幸的是SQLite只有ROUND()函数。
ROUND()
mspsb9vt1#
单元格:cast ( x as int ) + ( x > cast ( x as int ))
cast ( x as int ) + ( x > cast ( x as int ))
取x的整数部分,如果小数值大于0,则加1
楼层:cast ( x as int ) - ( x < cast ( x as int ))
cast ( x as int ) - ( x < cast ( x as int ))
取x的整数部分,如果小数值小于0,则减去1
示例
单元格:
SELECT (cast ( amount as int ) + ( amount > cast ( amount as int ))) AS amount FROM SALES WHERE id = 128;
楼层:
SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amount FROM SALES WHERE id = 128;我已经用MySQL ceil()和floor()函数检查了包括负数在内的所有极端情况。
SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amount FROM SALES WHERE id = 128;
ceil()
floor()
wnvonmuf2#
如果你从手头的数字中加上或减去0.5,你可以使用ROUND()来达到CEIL和FLOOR的效果。我更喜欢这个,因为它更可读。扩展Anees的示例:
CEIL
FLOOR
细胞:SELECT ROUND(amount+0.5, 0) AS amount FROM SALES WHERE id = 128;楼层:SELECT ROUND(amount-0.5, 0) AS amount FROM SALES WHERE id = 128;
SELECT ROUND(amount+0.5, 0) AS amount FROM SALES WHERE id = 128;
SELECT ROUND(amount-0.5, 0) AS amount FROM SALES WHERE id = 128;
感谢Anees的评论,我也没有想到这个角落的情况。他的解决方案更健壮。
tgabmvqs3#
请访问https://www.sqlite.org/lang_mathfunc.html#ceil显然,SQLite确实提供了上限:
ceil(X) ceiling(X)
返回第一个大于或等于X的可表示整数值。对于X的正值,此例程向零的方向舍入。对于X的负值,此例程向零的方向舍入。但是--遗憾的是,默认情况下并不编译它--并且只有在使用-DSQLITE_ENABLE_MATH_FUNCTIONS编译时选项编译合并时才是活动的。
-DSQLITE_ENABLE_MATH_FUNCTIONS
3条答案
按热度按时间mspsb9vt1#
公式
单元格:
cast ( x as int ) + ( x > cast ( x as int ))
取x的整数部分,如果小数值大于0,则加1
楼层:
cast ( x as int ) - ( x < cast ( x as int ))
取x的整数部分,如果小数值小于0,则减去1
示例
单元格:
SELECT (cast ( amount as int ) + ( amount > cast ( amount as int ))) AS amount FROM SALES WHERE id = 128;
楼层:
SELECT (cast ( amount as int ) - ( amount < cast ( amount as int ))) AS amount FROM SALES WHERE id = 128;
我已经用MySQL
ceil()
和floor()
函数检查了包括负数在内的所有极端情况。wnvonmuf2#
如果你从手头的数字中加上或减去0.5,你可以使用
ROUND()
来达到CEIL
和FLOOR
的效果。我更喜欢这个,因为它更可读。扩展Anees的示例:
细胞:
SELECT ROUND(amount+0.5, 0) AS amount FROM SALES WHERE id = 128;
楼层:
SELECT ROUND(amount-0.5, 0) AS amount FROM SALES WHERE id = 128;
感谢Anees的评论,我也没有想到这个角落的情况。他的解决方案更健壮。
tgabmvqs3#
请访问https://www.sqlite.org/lang_mathfunc.html#ceil
显然,SQLite确实提供了上限:
返回第一个大于或等于X的可表示整数值。对于X的正值,此例程向零的方向舍入。对于X的负值,此例程向零的方向舍入。
但是--遗憾的是,默认情况下并不编译它--并且只有在使用
-DSQLITE_ENABLE_MATH_FUNCTIONS
编译时选项编译合并时才是活动的。