#牛客创作赏金赛##记录你的求职/职场日常#最常用SQL Server和OracleMySQL中字符串截取主要包括三种截取方式,一是截取字符串前(后)几个字符,二是按照长度截取,三是按照分隔符截取。一、截取方式类型函数名描述截取前(后)几个字符left(str,n)返回字符串 str 的前 n 个字符right(str,n)返回字符串 str 的后 n 个字符按长度截取mid(str, start, length)从字符串 str 的 start 位置开始截取长度为 length 的子字符串substr(str, start, length)从字符串 str 的 start 位置开始截取长度为 length 的子字符串substring(str, start, length)从字符串 str 的 start 位置开始截取长度为 length 的子字符串按分隔符截取substring_index(str, delimiter, number)返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。如果 number 是正数,返回第 number 个字符左边的字符串。如果 number 是负数,返回第(number 的绝对值(从右边数))个字符右边的字符串二、实例select#返回字符串 student 中的前3个字符:left('student',3), #stu#返回字符串 student 的后两个字符:right('student',3),#ent#从字符串 'student' 中的第 2 个位置开始截取 3个 字符:mid('student', 2, 3), #tud substr('student', 2, 3), #tudsubstring('student', 2, 3), #tud #如果 number 是正数,返回正数第 number 个分隔符左边的字符串substring_index('student,学生,12',',',1), #studentsubstring_index('student,学生,12',',',2), #student,学生#如果 number 是负数,返回倒数第(绝对值number)个分隔符右边的字符串。substring_index('student,学生,12',',',-1), #12substring_index('student,学生,12',',',-2), #学生,12substring_index(substring_index('student,学生,12',',',-2),',',1) #学生#输出结果:stu|end|tud|tud|tud|student|student,学生|12|学生,12|学生求解代码方法一: month和yearselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere month(date) = 8 and year(date) = 2021方法二: date_formatselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere date_format(date,'%Y%m') = '202108'方法三: date_formatselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere date_format(date,'%y%m') = '2108' 方法四: likeselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere date like '2021-08%' 方法五: substringselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere substring(date,1,7) = '2021-08' 方法六: midselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere mid(date,1,7) = '2021-08'方法七: leftselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere left(date,7) = '2021-08'方法八: substring_indexselect count(distinct device_id) as did_cnt, count(device_id) as question_cntfrom question_practice_detailwhere substring_index(date,'-',2) = '2021-08'