取整函数和浮点计算函数，判断奇偶数，mysql序列号

最新推荐文章于 2024-09-22 17:00:19 发布

爱码～

最新推荐文章于 2024-09-22 17:00:19 发布

阅读量570

点赞数

分类专栏：苦练基本功文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_47120348/article/details/122964573

版权

苦练基本功专栏收录该内容

173 篇文章 1 订阅

订阅专栏

取整函数

- 取整函数
round(x) 表示该值四舍五入 无小数位
round(x,d) 表示x值四舍五入,d位小数
floor(x) 向下取整 返回整数 小数舍弃  floor(7/2) =3 
ceiling(x) 向上取整 返回整数 ceiling(7/2)=4 
mod(x,y) 取余数 select mod(3,2) 
- 判断奇偶数 select if(mod(5,2)>=1,'奇数','偶数')

序列号

- 排序列号
select @rownum := @rownum + 1 as rank,
table.* 
from (select @rownum := 0) r ,F0126 tblae
- 分组序号
SELECT
    @r:= case when @type=a.company then @r+1 else 1 end as rownum,
    @type:=a.company  as company,
    a.id as id,
		a.salary as salary
from 
    F0126 a ,(select @r:=0 ,@type:='') b

select * from (SELECT
    @r:= case when @type=a.company then @r+1 else 1 end as rownum,
    @type:=a.company  as company,
    a.id as id,
		a.salary as salary
from 
    F0126 a ,(select @r:=0 ,@type:='') b  )t where rownum in(
select n from (
select t.count,t.expr,if(t.expr = '奇数',ceiling(t.count/2),floor(t.count/2)) as n from (select count(1)as count,if(mod(count(1),2)>=1,'奇数','偶数')as expr from (SELECT
    @r:= case when @type=a.company then @r+1 else 1 end as rownum,
    @type:=a.company  as company,
    a.id as id,
		a.salary as salary
from 
    F0126 a ,(select @r:=0 ,@type:='') b  )t 
group by company)t)t)