SQL案例
1.
select e.employee_name AS MANAGER,ROW_NUMBER()over(PARTITION by e.date order by e.salary DESC) AS RN FROM
(
(select DISTINCT b.manager_id,a.employee_name from Employee a inner join Employee b on a.employee_id = b.manager_id) c
left join
(select employee_id,salary,date from Salary) d on c.manager_id = d.employee_id
) e
WHERE RN = 1
2.
select d.employee_id,d.employee_name,d.SalaryByYear from (
(
select a.employee_id,a.employee_name from Employee a where a.employee_id not in
(select distinct manager_id from Employee)
) b
left join
(
select employee_id,SUBSTR(date,1,4) as year,sum(salary) as SalaryByYear from Salary GROUP by employee_id,SUBSTR(date,1,4)
) c
on b.employee_id = c.employee_id
)d order by d.SalaryByYear desc limit 1
3.
select DISTINCT b.COMPANYCOD from
(
(select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD,count(*) as count from sheet1 WHERE ADJDIRECT = "调入方" GROUP BY BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD) a
INNER JOIN
(select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD,count(*) as count from sheet1 WHERE ADJDIRECT = "调出方" GROUP BY BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD) b
on a.BILLNUMBERID = b.BILLNUMBERID and a.ORDERNO = b.ORDERNO and a.COMPANYCOD=b.COMPANYCOD and a.count = b.count
)
4.
select distinct b.COMPANYCOD from
(
(select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD,count(*) as count from sheet1 WHERE ADJDIRECT = "调入方" GROUP BY BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD having count=1) a
INNER JOIN
(select BILLNUMBERID,ORDERNO,ADJDIRECT,COMPANYCOD from sheet1 WHERE ADJDIRECT = "调出方") b
on a.BILLNUMBERID = b.BILLNUMBERID and a.ORDERNO = b.ORDERNO and a.COMPANYCOD=b.COMPANYCOD
)
5.
select a.BILLNUMBERID,sum(ADJUSTMONEY + AFTERTAXMONEY) AS 调整总金额 from
(SELECT distinct BILLNUMBERID,ADJUSTWAYC from sheet1 where ADJUSTWAYC = "双向调整") a
INNER JOIN sheet1 b on a.BILLNUMBERID = b.BILLNUMBERID
GROUP BY a.BILLNUMBERID
6.删除工资大于所在部门平均工资的员工记录。
delete from emp where sal>(select avg(sal) from emp where deptno=emp1.deptno);
7.查询大于自己部门平均工资的员工姓名、工资、所在部门平均工资、高于部门平均工资的额度。
select ename,sal,avgsal,sal-avgsal from emp e,(select deptno,avg(sal) avgsal from emp group by deptno )d where e.deptno=d.deptno and e.sal>d.avgsal;
8.查询所有工作在NEW YORK和CHICAGO的员工姓名、员工编号，以及他们的经理姓名、经理编号。
select e.ename,e.empno,m.ename,m.empno from emp e,emp m,dept d where e.mgr=m.empno and e.deptno=d.deptno and d.loc in(‘NEW YORK’,‘CHICAGO’);
9.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名、职位，不包括10部门员工。
select ename,job from emp where (mgr,job)in(select job,mgr from emp where deptno=10) and deptno<>10;
10.列出一个雇员都没有的所有部门名称。
select dname from dept d where not exists(select 1 from emp where deptno=d.deptno);
11.列出至少有一个雇员的所有部门名称。
select dname from dept d where exists (select 1 from emp where deptno=d.deptno);
12查询入职日期在1982年1985年的员工姓名，入职日期。
select ename,hiredate from emp where extract(year from hiredate) between 1982 and 1985;
13、查询入职日期最早的前5名员工姓名。
答案： select ename,hiredate from emp e where (select count(empno) from emp where hiredate<e.hiredate)<5;
14.
DELETE
FROM
exam_record
WHERE
timestampdiff(
MINUTE,
start_time,
submit_time
)<5
AND score < 60
timestampdiff 查询两个时间以第一参数为单位的差
timestampdiff 时间差函数，查询两个时间之间的差值，有参数设置， 可以精确到天(day) 小时(hour) 分钟(minute) 和秒(second) 而datediff函数，返回值是相差的天数，不能定位到小时和分钟秒数。
15.Mysql中Date_formate函数
SELECT id,
DATE_FORMAT(submit_time,'%Y%m') as month,
DATE_FORMAT(start_time, '%Y-%m-%d %H:%i:%S') start_time,
DATE_FORMAT(end_time, '%Y-%m-%d %H:%i:%S') end_time
FROM user；
16.Mysql中提取年，月，日信息
select order_num,order_date
from Orders
where month(order_date)='01' and year(order_date)='2020'
order by order_date
17.月总刷题数和日均刷题数
1.先分别取出每个月的天数和年份，这里用到的是dayofmonth和date_format 然后分别统计每个月的刷题总数，和日均刷题数， 最后利用with rollup 进行统计，with rollup所在的行为'2021汇总' 利用coalesce函数添加‘2021汇总’ 一般主流数据库系统都支持该coalesce函数 该函数主要用来进行空值处理
select coalesce(year_mon,'2021汇总') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id)/max(t.days_month),3) as avg_day_cnt
from
(select question_id,
dayofmonth(last_day(submit_time)) as days_month,
date_format(submit_time,'%Y%m') as year_mon
from practice_record
where year(submit_time)=2021) as t
group by t.year_mon
with rollup;
18.未完成试卷数大于1的有效用户
获取各用户的tag,start_time及未完成标记和已完成标记，如果该作答记录交卷了则已完成标记为1，未完成标记为0，否则相反：if(submit_time is null, 1, null) as incomplete
对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)
对于一个人（组内）的多条作答，用;连接去重后的作答记录：group_concat(distinct concat_ws(':', date(start_time), tag) SEPARATOR ';')
筛选未完成试卷作答数大于1的有效用户：having complete_cnt >= 1 and incomplete_cnt BETWEEN 2 and 4
select uid,count(incomplete) as incomplete_cnt,count(complete) as complete_cnt,
group_concat(distinct concat_ws(':',date(start_time),tag) separator ';') as detail
from
(
select a.uid,b.tag,a.start_time,
if(a.submit_time is null,1,null) as incomplete,
if(a.submit_time is null,null,1) as complete
from exam_record a left join examination_info b
on a.exam_id = b.exam_id
where year(a.start_time) =2021
) t
group by uid having complete_cnt >= 1 and incomplete_cnt between 2 and 4
order by incomplete_cnt desc
19.like模糊查询
-
_ ：下划线 代表匹配任意一个字符；
-
% ：百分号 代表匹配0个或多个字符；
-
[]: 中括号 代表匹配其中的任意一个字符；
-
[^]: ^尖冒号 代表 非，取反的意思；不匹配中的任意一个字符。
1）查询学生表表中名字的第2个字为“小”或“大”的学生的姓名和学号
select 姓名,学号 from student where 姓名 like '_[大小]%';
2）从学生表表中查询学号的最后一位不是2、3、5的学生信息
select 姓名，年龄，学号 from student where 学号 like '%[^235]';
3）查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况
select 姓名，年龄，学号 from student where 姓名 like '[李张刘]%';
20.union和union用法
使用 UNION可以实现将多个查询结果集合并为一个结果集。
sleect 语句1
union [all] # all表示不去重
select 语句2；
注意：
所有查询语句中列的个数和列的顺序必须相同。
所有查询语句中对应列的数据类型必须兼容。
ORDER BY语句要放在最后一个查询语句的后边。
21.case when和if函数的运用
case when age < 25 or age is null then '25岁以下'
when age >= 25 then '25岁及以上' end age_cut,count(*) as number
from user_profile
group by age_cut;
if(age>=25,'25岁及以上','25岁以下') as age_cut,
count(*) as number
from user_profile group by age_cut;
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'；
COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号；
4）现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0
select a.device_id,a.university,
# 方式一
# sum(if(YEAR(date) = '2021' and MONTH(date) = 8 ,1,0)) as question_cnt,
# sum(if(YEAR(date) = '2021' and MONTH(date) = 8 and result='right',1,0)) as right_question_cnt
# 方式二
sum(case when YEAR(date)='2021' and month(date) = 8 then 1 else 0 end) as question_cnt,
sum(case when YEAR(date)='2021' and month(date) = 8 and result='right' then 1 else 0 end) as right_question_cnt
from user_profile a left join question_practice_detail b
on a.device_id = b.device_id
where a.university = '复旦大学'
group by a.device_id
5）现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。
select
c.difficult_level,
# 方式一
avg(if(b.result='right',1,0)) as correct_rate
# 方式二
# avg((case when b.result='right' then 1 else 0 end)) as correct_rate
# 方式三
# sum(if(b.result ='right',1,0)) / count(b.question_id) as correct_rate
from
user_profile a inner join question_practice_detail b
on a.device_id = b.device_id
inner join question_detail c
on b.question_id = c.question_id
where a.university = '浙江大学'
group by c.difficult_level
order by correct_rate;
22.计算用户的平均次日留存率
1）方式一
解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。
解法1：可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率
select count(t2.device_id) / count(t1.device_id) as avg_ret
from
(select distinct device_id,date from question_practice_detail) as t1
left join
(select distinct device_id,date from question_practice_detail) as t2
on t1.device_id = t2.device_id and t2.date = date_add(t1.date,interval 1 day);
2）方式二
解法2：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)
解法2：检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。
select avg(if(datediff(date2,date1)=1,1,0)) as avg_ret
from (
select distinct device_id,date as date1,lead(date)over(partition by device_id order by date) as date2
from
(select distinct device_id,date from question_practice_detail) as t
) as id_last_next_date;
23.mysql字符串截取函数
1）统计每种性别的人数
profile：180cm,75kg,27,male
1.1）方式一
字段SUBSTRING_INDEX的写法
select substring_index(profile,',',-1) as gender,count(*) as number
from user_submit group by gender;
1.2）方式二
LIKE的写法
select if(profile like '%female','female','male') as gender,count(*) as number
from user_submit group by gender;
2）提取博客URL中的用户名
字段blog_url：http:/url/bisdgboy777
# 方式一 字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
select device_id,substring_index(blog_url,'/',-1) as user_name from user_submit;
# 方式二 替换法 replace(string, '被替换部分','替换后的结果')
select device_id,replace(blog_url,'http:/url/','') as user_name from user_submit;
# 方式三 截取法 substr(string, start_point, length*可选参数*)
select device_id,substr(blog_url,11,length(blog_url)-10) as user_name from user_submit;
# 方式四 删除法 trim('被删除字段' from 列名)
select device_id,trim('http:/url/' from blog_url) as user_name from user_submit;
3）提取profile中的年龄
profile：180cm,75kg,27,male
截取函数的嵌套使用
select substring_index(substring_index(profile,',',3),',',-1) as age,
count(*) as number from user_submit group by age;
24.输出2021年里，所有每次试卷得分都能到85分的人以及至少有一次用了一半时间就完成高难度试卷且分数大于80的人的id和活动号，按用户ID排序输出。
主要的是activity1 as activity这种写法，且timestampdiff函数中不能是minute而具体到秒才可以select b.uid,'activity1' as activity
from exam_record b
where year(b.submit_time) = '2021'
group by b.uid having min(b.score) >= 85
union all
select distinct b.uid,'activity2' as activity
from examination_info a inner join exam_record b on a.exam_id=b.exam_id
where year(a.release_time) = '2021' and a.tag = 'SQL'
and a.difficulty = 'hard' and timestampdiff(minute, b.start_time, b.submit_time) * 2 < a.duration
and b.score > 80
order by uid；
25.找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。
- rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
- row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
- dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)
a.group by的常见搭配：常和以下聚合函数搭配
- avg（）-- 求平均值
- count（）-- 计数
- sum（）-- 求和
- max（） -- 最大值
- min（）-- 最小值
b.group by 的进阶用法，和with rollup一起使用。
select t.tag,t.uid,t.ranking
from (
select a.tag,b.uid,
row_number()over(partition by a.tag order by max(b.score) desc,min(b.score) desc,b.uid desc) as ranking
from
examination_info a left join exam_record b
on a.exam_id=b.exam_id
group by a.tag,b.uid) t
where t.ranking<=3;
26.请你找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序。
第一步，我先从统计那步下手，分别将exam_record完成试卷数 和 practice_record题目练习数一一算出来
第二步，我着重于它的筛选条件“高难度SQL试卷得分平均值大于80并且是7级的红名大佬”/
uid,
exam_cnt,
if(question_cnt is null, 0, question_cnt)
from
(select
uid,
count(submit_time) as exam_cnt
from exam_record
where YEAR(submit_time) = 2021
group by uid) t
left join
(select
uid,
count(submit_time) as question_cnt
from practice_record
where YEAR(submit_time) = 2021
group by uid) t2 using(uid)
where uid in
(
select
uid
from exam_record
join examination_info using(exam_id)
join user_info using(uid)
where tag = 'SQL' and difficulty = 'hard' and `level` = 7
group by uid
having avg(score) >= 80
)
order by exam_cnt asc, question_cnt desc
- 找到每份试卷的ID、限制时间、发布时间以及每份试卷被完成的耗时、耗时降序排名和增序排名。这里的不用去重，也不用分组，因为每一份都要计算耗时：
- 试卷信息与做题信息分布在两个表中，因此要将两个表以exam_id连接。知识点：join...on...
- 只查询有提交时间的时间，筛掉没做完的试卷。知识点：where
- 试卷ID、限制时间、发布时间直接获取，完成的耗时使用timestampdiff函数根据开始时间和提交时间计算分钟数差值。知识点：timestampdiff()
- 利用分组聚合排名对每一种试卷的完成耗时分别进行增序排名和降序排名。知识点：row_number() over partition by
- 查询出的表格记为table1
- 根据上面筛选出来的信息查询每份试卷的限制时间、发布时间及第二快与第二慢的差值：
- 每张不同的试卷都会有一个数据，因此以试卷ID作为分组。知识点：group by
- 每组试卷的ID、限制时间、发布时间都可以由table1直接查询到。
- 将每组试卷的完成时间累加，只有当最慢排名为2时才加正值，最快排名为2时加负值，其余情况加0.这样刚好是用时第二多减去用时第二少。
sum(case when rank1 = 2 then costtime when rank2 = 2 then -costtime else 0 end) as sub知识点：sum()、case when...then...when...then...else...end - 查询出的结果记为table2
- 最后从table2出筛选出大于等于限制时间一半的试卷ID，限制时间和发布时间
- 输出按照试卷ID的降序排列。知识点：order by
select distinct t2.exam_id,t2.duration,t2.release_time
from(
select t1.exam_id,t1.duration,t1.release_time,
sum(case when t1.rank1 = 2 then costtime when t1.rank2 = 2 then -costtime else 0 end) as subtime
from(
SELECT a.exam_id,a.duration,a.release_time,
timestampdiff(minute,b.start_time,b.submit_time) as costtime,
row_number()over(partition by a.exam_id order by timestampdiff(minute,b.start_time,b.submit_time) desc) as rank1,
row_number()over(partition by a.exam_id order by timestampdiff(minute,b.start_time,b.submit_time) asc) as rank2
FROM
examination_info a left join exam_record b on a.exam_id=b.exam_id
where b.submit_time is not null
) t1
group by t1.exam_id) t2
where t2.subtime * 2 >= t2.duration
order by t2.exam_id desc
28）请统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。
- rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)\
- row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
- dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)
- percent_rank() over() 按照数字所在的位置进行百分位分段
- ntile(n)over() 将数字按照大小平均分成n段
- lead(字段名，n)over()把字段数据向前移n个单元格
- lag(字段名，n)over()把字段数据向后移n个单元格
案例：
select * ,
row_number()over(order by uid) row_number1, /*按照uid的大小不重不漏1 2 3 4 5 6 7 */
rank()over(order by uid) rank1, /*按照uid的大小并列第一无第二，1 1 1 4 5 5 7*/
dense_rank()over(order by uid) dense_rank1,/*按照uid的大小并列第一有第二，1 1 1 2 3 3 4*/
percent_rank()over(order by uid) percent_rank1,/*按照uid的大小进行百分法排序*/
ntile(2)over(order by uid) ntile1,/*按照uid的大小，把uid评价分成2组*/
lead(uid)over(order by uid) lead1,/*把uid向上推1个位置*/
lag(uid)over(order by uid) lag1 /*把uid向下推1个位置*/
from user_id;
解答：
select t.uid,date_format(start_time,'%Y%m') start_month, -- 取月份数
count(start_time) as total_cnt, -- 答题数
count(submit_time) as complete_cnt -- 完成数
from
(
select *,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as time_rk-- 对作答时间排序
from exam_record ) t
right join
(
select * from (
select uid,
percent_rank()over(order by count(submit_time)/ count(start_time)) as rate_rk
from exam_record where exam_id in (select exam_id from examination_info where tag = 'SQL')
group by uid) A -- 对用户完成率进行排序
where A.rate_rk <= 0.5 -- 查找完成率低于50%
and A.uid in (select uid from user_info where level in (6,7)) -- 查找6,7级用户
) t2 on t.uid = t2.uid
where t.time_rk <= 3 -- 查找作答时间最近3个月
group by t.uid,date_format(start_time,'%Y%m')
order by t.uid,date_format(start_time,'%Y%m') -- 按照用户id和月份进行升序排序
29）请你将用户作答高难度试卷的得分在每份试卷作答记录内执行min-max归一化后缩放到[0,100]区间，并输出用户ID、试卷ID、归一化后分数平均值；最后按照试卷ID升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）
主要考察聚类窗口函数，和聚类窗口函数的用法和GROUP BY 函数类似。
- MIN()OVER() :不改变表结构的前提下，计算出最小值
- MAX()OVER():不改变表结构的前提下，计算出最大值
- COUNT()OVER():不改变表结构的前提下，计数
- SUM()OVER():不改变表结构的前提下，求和
- AVG()OVER():不改变表结构的前提下，求平均值
- percent_rank() over() 按照数字所在的位置进行百分位分段
- ntile(n)over() 将数字按照大小平均分成n段
- lead(字段名，n)over()把字段数据向前移n个单元格
- lag(字段名，n)over()把字段数据向后移n个单元格
解答：
30）请输出每份试卷每月作答数和截止当月的作答总数。
31）请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。
若用户的登录时间等于首次登录时间则定义为新用户，新用户被标记为1，# 对空值的处理方式：
32）现有试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）请统计有未完成状态的试卷的未完成数incomplete_cnt和未完成率incomplete_rate
33）现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）请输出每个0级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和0分处理。
34）高级条件语句
找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母c开头的试卷类别（如C,C++,c#等）的已完成的试卷ID和平均得分，按用户ID、平均分升序排序。
RLIKE后面可以跟正则表达式。
1、字符^
意义：表示匹配的字符必须在最前边。
例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。
2、字符$
意义：与^类似，匹配最末的字符。
例如：t$不匹配“eater”中的‘t’，但匹配“eat”中的‘t’。
3、字符[0-9]
意义：字符列表，匹配列出中的任一个字符。你可以通过连字符-指出字符范围。
例如：[abc]跟[a-c]一样。它们匹配“brisket”中的‘b’和“ache”中的‘c’。
4、字符+
意义：匹配+号前面的字符1次及以上。等价于{1,}。
例如：a+匹配“candy”中的‘a’和“caaaaaaandy”中的所有‘a’。
解答：
35）exists的用法
请你筛选表中的数据，当有任意一个0级用户未完成试卷数大于2时，输出每个0级用户的试卷未完成数和未完成率（保留3位小数）；若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。
36）为了得到用户试卷作答的定性表现，我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留3位小数），未完成过试卷的用户无需输出，结果按用户等级降序、占比降序排序。
37）录题同学有一次手误将部分记录的试题类别tag、难度、时长同时录入到了tag字段，请帮忙找出这些录错了的记录，并拆分后按正确的列类型输出。
一、知识点梳理
a、索引式提取substring_index(字段名,'索引符号',位置)
b、直接提取substring(字段名，开始位置，结束位置)
c、计算字符串长度char_length(字段名) 和 length(字段名)
- length(字段名)汉字算3个字符
- char_length(字段名) 汉字算1个字符
38）输出昵称字符数大于10的用户信息，对于字符数大于13的用户昵称输出前10个字符然后加上三个点号：『...』
- 筛选昵称字符数大于10的用户：WHERE CHAR_LENGTH(nick_name) > 10
- 对字符数大于13的用户昵称做处理：IF(CHAR_LENGTH(nick_name) > 13,
- 前10个字符加上三个点号：CONCAT(SUBSTR(nick_name, 1, 10), '...')
解答：
39）试卷的类别tag可能出现大小写混乱的情况，请先筛选出试卷作答数小于3的类别tag，统计将其转换为大写后对应的原本试卷作答数。如果转换后tag并没有发生变化，不输出该条结果。
c、直接提取substring(字段名，开始位置，结束位置)
d、计算字符串长度char_length(字段名) 和 length(字段名)
- 统计每类试卷的作答数（区分大小写），生成临时表 t1:
- 左连接试卷作答表和试卷信息表：exam_record LEFT JOIN examination_info USING(exam_id)
- 按试卷类别分组：GROUP BY tag
- 统计每类试卷作答数：SELECT tag, COUNT(uid) as answer_cnt
- 对表t1进行自连接
- 选出满足题目条件的结果：
- a.tag转大写后和b.tag一样：ON UPPER(a.tag) = b.tag
- a.tag转换后必须发生变化：a.tag != b.tag
- a的试卷作答数小于3：a.answer_cnt < 3
解答：
40）统计2021年11月每天新用户的次日留存率（保留2位小数）
- 次日留存率为当天新增的用户数中第二天又活跃了的用户数占比。
- 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序。
- 先查询出每个用户第一次登陆时间（最小登陆时间）--每天新用户表
- 因为涉及到跨天活跃，所以要进行并集操作，将登录时间和登出时间取并集，这里union会去重--用户活跃表
- 将每天新用户表和用户活跃表左连接，只有是同一用户并且该用户第2天依旧登陆才会保留整个记录，否则右表记录为空
- 得到每天新用户第二天是否登陆表后,开始计算每天的次日留存率：根据日期分组计算，次日活跃用户个数/当天新用户个数
