mysql应用函数汇总
- 目的
- 函数1 -oracle中的 decode 替代方式
- 函数2 - lag 与 lead
- 函数3 - over
- 函数4 - IF
- 函数5 find_in_set
目的
mysql函数可以减少大量的后端代码,以此来记录一些常用的sql函数
函数1 -oracle中的 decode 替代方式
解释:类似与一种语法糖函数
参数:decode(参数1,if,then,if,then,else)
参数1为字段名,if作为判断,then作为判断后的结果。
关联:实现转阵
实践:
select student,
sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from (
select student,subject,grade from point
) as a
GROUP BY student;
替代采用case when 表达式 then 确认走的流程 else 错误走的流程 end
SELECT
student,
sum( case when subject = '语文' then grade else NULL end) as "语文",
sum( case when subject = '数学' then grade else NULL end) as "数学",
sum( case when subject = '英语' then grade else NULL end) as "英语"
FROM
( SELECT student, SUBJECT, grade FROM point ) AS a
GROUP BY
student;
截图:
函数2 - lag 与 lead
解释:以列的形式进行获取数据的前一行或者后一行的数据
参数:lag(参数1, 参数2,参数3) 参数1 是字段名 ,参数2 是偏移量,参数3 是默认值
lead同上 向下一条取值
关联:与over函数联用,主要是基于排序为主。
实践:
求出语文排名,并求出每位同学前一名的同学分数
select *, lag(grade,1,'-')over(order by grade desc) last from (select student, grade,subject,rank()over(partition by subject order by grade desc) sort from point) t where subject ="语文"
截图
lead参数一致,向下取值。
函数3 - over
解释:分组多行函数,用于曾现列
参数:over(partition by 参数1 order by 参数 2) 参数1 分区的列 参数2 排序的列
关联:group by (用于分组一条函数,与聚合函数结合)
联系:group by与聚合函数才能达到自身的需求,而over函数也需要与rank() row_number() dense_rank() 使用
实践:
查询各科排名第一的学生
SELECT
*
FROM
( SELECT student, grade, SUBJECT, row_number ( ) OVER ( PARTITION BY SUBJECT ORDER BY grade DESC ) mm FROM point ) t
WHERE
mm = 1
截图
查询各科排名情况
SELECT
*
FROM
( SELECT student, grade, SUBJECT, dense_rank ( ) OVER ( PARTITION BY SUBJECT ORDER BY grade DESC ) FROM point ) t
截图
注意 rank() 和 dense_rank()的区别 rank()是跳跃排序,如果有两个第二,默认第三的排序会排到第四。而dense_rank()则是连续排序。
函数4 - IF
解释:if(表达式,值1,值2) 值1为true的时候,值2为false的时候
关联:类似于三元表达式
实践:
select *, if(grade > 60,"及格","不及格") as status from point
截图
函数5 find_in_set
find_in_set(str,strList)
参数 str 为字符,需要寻找字符
参数strList ,为以逗号隔开的字符串
用途:用于在“1,2,3”这样的字符串中寻找存在的字符串的数据
select * from `user` where FIND_IN_SET('2',text)
结果
利用find_in_set可以很有效的查询以逗号隔开的数据
未完,待更新