mysql字段截取/判断字符串出现的次数

七&仔

已于 2022-07-30 17:44:53 修改

阅读量2.3k

点赞数

分类专栏： mysql 文章标签： sql

于 2022-07-30 14:20:39 首次发布

本文链接：https://blog.csdn.net/zk_jy520/article/details/126071633

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.mysql字段截取

代码：

subString_index(code,'_',2)

结果：

eg1：

eg2：

eg3:

2.mysql判断字符串出现的次数

length(code) - length(replace(code,'_',''))

eg：

3.mysql数据处理（闲的）

代码

select subString_index(evaluation_stars,'-',1) as stars1,
right(subString_index(evaluation_stars,'-',2),1) as stars2,
subString_index(evaluation_stars,'-',-1) as stars3,
evaluation_stars 

from meeting where
length(evaluation_stars) - length(replace(evaluation_stars,'-','')) = 2

再求个平均数，再保留2位小数

代码

select
format(avg(subString_index(evaluation_stars,'-',1)),2) as avg_stars1,
format(avg(right(subString_index(evaluation_stars,'-',2),1)),2) as avg_stars2,
format(avg(subString_index(evaluation_stars,'-',-1)),2) as avg_stars3

from oc_itc_meeting where
length(evaluation_stars) - length(replace(evaluation_stars,'-','')) = 2

再取出总和的平均值，和总数

代码

select
format(avg(subString_index(evaluation_stars,'-',1)),2) as avg_stars1,
format(avg(right(subString_index(evaluation_stars,'-',2),1)),2) as avg_stars2,
format(avg(subString_index(evaluation_stars,'-',-1)),2) as avg_stars3,
format(avg(subString_index(evaluation_stars,'-',1) + right(subString_index(evaluation_stars,'-',2),1) + subString_index(evaluation_stars,'-',-1)) / 3,2) as avg_all,
count(*) as num

from oc_itc_meeting where
length(evaluation_stars) - length(replace(evaluation_stars,'-','')) = 2