sql常用操作之文本转换函数

题1

https://www.nowcoder.com/practice/a5475ed3b5ab4de58e2ea426b4b2db76?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select exam_id,substring_index(tag, ',', 1) as tag,substring_index(substring_index(tag, ',', 2), ',', -1) as  difficulty,substring_index(tag, ',', -1) as duration
from examination_info
where tag like '%,%';

题2

https://www.nowcoder.com/practice/69243e2742bf47f1976db836860c123c?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select uid,if(CHAR_LENGTH(nick_name)>13, CONCAT(SUBSTRING(nick_name, 1 ,10),'...') , nick_name) as nick_name # substr也ok.
from user_info
where CHAR_LENGTH(nick_name) > 10

select uid,CASEWHEN CHAR_LENGTH(nick_name) > 13 THEN CONCAT(LEFT(nick_name, 10), '...')ELSE nick_name END AS nick_name
from user_info
where CHAR_LENGTH(nick_name) > 10

题3

https://www.nowcoder.com/practice/81cb12931a604811ae69d332515c7734?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

1.先统计每类试卷的作答数（区分大小写），生成临时表 t_tag_count:
2.对临时表t_tag_count进行自连接，假设取出的两条记录分别为a和b：t_tag_count as a JOIN t_tag_count as b
选出满足题目条件的结果：
a.tag转大写后和b.tag一样：ON UPPER(a.tag) = b.tag
a.tag转换后必须发生变化：a.tag != b.tag
a的试卷作答数小于3：a.answer_cnt < 3

WITH t_tag_count as (SELECT tag, COUNT(exam_record.exam_id) as answer_cntFROM exam_record JOIN examination_info USING(exam_id) # 本题left join也ok.GROUP BY tag
)SELECT a.tag, b.answer_cnt
FROM t_tag_count as a JOIN t_tag_count as b ON UPPER(a.tag) = b.tag 
#     and a.tag != b.tag and a.answer_cnt < 3; 
where a.tag != b.tag and a.answer_cnt < 3;

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！