做题方法:对于难度较大的题目,建议采用三步走的方法
第一步:看懂题目,了解需求,看一下别人的代码,理解思路
第二步:自己基本上能够把代码写下来,但是还存在一定的问题
第三部:能够完整无错误写出代码
SQL1 插入记录(一)
- 注解
1、insert into…values 如果要插入多行,指令是 values ,…多行之间用逗号隔开;
2、Auto Increment类型的列,在插入时用null/default关键字补位,在插入时sql会自动计算各行应有的值;
3、interval 时间间隔关键字,常和date_add() 或 date_sub()搭配使用。
insert into exam_record values
(null,1001,9001,"2021-09-01 22:11:12","2021-09-01 22:11:12"+interval 50 minute,90),
(null,1002,9002,"2021-09-04 07:01:02",null,null);
SQL2 插入记录(二)
insert into exam_record_before_2021
select null,uid,exam_id,start_time,submit_time,score
from exam_record
where score is not null and year(submit_time)<2021
SQL3 插入记录(三)
- 注解
REPLACE INTO 跟 INSERT 功能类似
不同点在于:REPLACE INTO 首先尝试插入数据到表中,
如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,
然后插入新的数据;否则,直接插入新数据。
要注意的是:插入数据的表必须有主键或者是唯一索引!
否则的话,REPLACE INTO 会直接插入数据,这将导致表中出现重复的数据。
replace into examination_info
(exam_id,tag,difficulty,duration,release_time) values
(9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00');
SQL4 更新记录(一)
update examination_info
set tag = 'Python'
where tag = 'PYTHON'
SQL5 更新记录(二)
- 注解
date_format(start_time,"%Y-%m-%d")<“2021-09-01”
update exam_record
set submit_time='2099-01-01 00:00:00',score=0
where date_format(start_time,"%Y-%m-%d")<"2021-09-01" and submit_time is null;
SQL6 删除记录(一)
- 注解
date_sub(start_time,interval -5 minute) sub -5 就是负负得正的意思
date_add(start_time,interval +5 minute)
时间差函数timestampdiff(minute,start_time,submit_time)
timestampdiff函数返回begin-end的结果,其中begin和end是DATE或DATETIME表达式。
timestampdiff函数允许其参数具有混合类型,例如,begin是DATE值,end可以是DATETIME值。 如果使用DATE值,则TIMESTAMPDIFF函数将其视为时间部分为“00:00:00”的DATETIME值。
unit参数是确定(end-begin)的结果的单位,表示为整数。 以下是有效单位:
microsecond/second/minute/hour/day/week/month
- 解法一
delete
from exam_record
where submit_time < date_sub(start_time,interval -5 minute) and score < 60
- 解法二
delete
from exam_record
where submit_time < date_add(start_time,interval +5 minute) and score < 60
- 解法三
delete
from exam_record
where timestampdiff(minute, start_time, submit_time ) < 5
and score < 60;
SQL7 删除记录(二)
delete
from exam_record
where timestampdiff(minute,start_time,submit_time ) < 5 or submit_time is null
order by start_time asc
limit 3
SQL8 删除记录(三)
- DROP TABLE, TRUNCATE TABLE, DELETE TABLE 三种删除语句的区别
1.DROP TABLE 清除数据并且销毁表,是一种数据库定义语言(DDL), 执行后不能撤销,被删除表格的关系,索引,权限等等都会被永久删除。
2.TRUNCATE TABLE 只清除数据,保留表结构,列,权限,索引,视图,关系等等,相当于清零数据,是一种数据库定义语言(DDL) ,执行后不能回滚,删除后没有返回值,不能加where 条件。
3.DELETE TABLE 删除(符合某些条件的)数据,是一种数据操纵语言(DML Data Manipulation Language),执行后可以回滚,删除后有返回值,可以加where 条件。
4.运行速度一般DROP最快,DELETE最慢,但是DELETE最安全。
5.假如要删除的表中有自增长列,如果用delete 删除后,再插入数据,自增长列的值从断点开始,而truncate删除后,再插入数据,自增长列的值从1开始。
truncate table exam_record
SQL9 创建一张新表
create table if not exists user_info_vip(
id int(11) primary key auto_increment comment '自增ID',
uid int(11) unique key not null comment '用户ID',
nick_name varchar(64) comment '昵称',
achievement int(11) default 0 comment '成就值',
level int(11) comment '用户等级',
job varchar(32) comment '职业方向',
register_time datetime default CURRENT_TIMESTAMP comment'注册时间'
)
SQL10 修改表
alter table user_info add school varchar(15) after level;
-- 增加列在某列之后
-- alter table 增加的表格 add 增加列的名称 数据类型 位置(after level 在level 之后)
alter table user_info change job profession varchar(10);
-- 更换列的名称及数据类型
-- alter table user_info change 原列名 修改列名 修改数据类型
alter table user_info modify achievement int(11) default 0;
-- 更改数据类型
-- alter table 表名 modify 修改列名称 数据类型 默认值等
SQL11 删除表
drop table if exists exam_record_2011;
drop table if exists exam_record_2012;
drop table if exists exam_record_2013;
drop table if exists exam_record_2014;
SQL12 创建索引
-- 唯一索引
alter table examination_info
add unique index uniq_idx_exam_id(exam_id);
-- 全文索引
alter table examination_info
add fulltext index full_idx_tag(tag);
-- 普通索引
alter table examination_info
add index idx_duration(duration);
SQL13 删除索引
- 方法一
drop index uniq_idx_exam_id on examination_info;
drop index full_idx_tag on examination_info;
- 方法二
alter table examination_info drop index uniq_idx_exam_id;
alter table examination_info drop index full_idx_tag;
SQL14 SQL类别高难度试卷得分的截断平均值
select
i.tag
,i.difficulty
,round((sum(r.score)-max(r.score)-min(r.score))/(count(r.score)-2),1)
from examination_info i
left join exam_record r on i.exam_id=r.exam_id
where i.tag='SQL' and i.difficulty='hard' and r.score is not NULL;
SQL15 统计作答次数
select
count(1) total_pv
,count(score) complete_pv
,count(distinct exam_id and score is not null) complete_exam_cnt
from exam_record
SQL16 得分不小于平均分的最低分
- 解法一
-- 方法1:用窗口函数直接求出avg,作为一列。然后score与avg比较
select min(t.score)
from (
select
r.score
,avg(r.score) over() avg_score
from exam_record r
left join examination_info i on r.`exam_id`=i.`exam_id`
where i.`tag`='SQL' and r.score is not null
) t
where t.score>=t.avg_score
- 解法二
-- 方法2:聚合函数求出平均值,作为表中的一个筛选条件
select min(score)
from exam_record r
left join examination_info i on r.exam_id=i.exam_id
where i.tag="SQL" and r.score is not null
and r.score>=(
select avg(score)
from exam_record r
left join examination_info i on r.exam_id=i.exam_id
where i.tag="SQL" and r.score is not null
)
SQL17 平均活跃天数和月活人数
- 注解
主要难的一点是天数的计算。到底是count(distinctuid,date_format(start_time,"%Y%m%d"))
还是count(start_time)作为分子呢???
关键是理解题目的意思是:天数。
假设一个uid 比如1001在2021-07-06这一天有二个记录,如果是count(start_time)那么就是天数是2
但是如果是count(distinct uid,date_format(start_time,"%Y%m%d"))天数就是1了,因为只有2021-07-06只有一天存在!
所以关键是明白一天二个不同时间段是一天,还是二天
select
date_format(submit_time,"%Y%m") month
,round(count(distinct uid,date_format(submit_time,"%Y%m%d"))/count(distinct uid),2) avg_active_days
,count(distinct uid) mau
from exam_record
where score is not null and year(submit_time)=2021
group by date_format(submit_time,"%Y%m")
SQL18 月总刷题数和日均刷题数
- 注解
day(last_day(submit_time) 该函数用于求当月的总天数
select
date_format(submit_time,"%Y%m") submit_month
,count(score) month_q_cnt
,round(count(score)/day(last_day(submit_time)) ,3) avg_day_q_cnt
from practice_record
where year(submit_time) =2021
group by date_format(submit_time,"%Y%m")
union all
select
"2021汇总" submit_month
,count(1) month_q_cnt
,round(count(score)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time) =2021
order by submit_month
SQL19 未完成试卷数大于1的有效用户
-
注解
1、统计未完成的列的时候要用submit_time,有的试卷没有提交确是有分数的
2、函数group_concat()
group_concat( distinct concat(date(e.start_time),’:’,i.tag)
order by e.start_time separator ‘;’) -
解法一
-- 有效用户
select uid
from exam_record
where year(start_time) = 2021
group by uid
having count(score)>=1 and (count(1)-count(score))<5
-- 完整代码
select
e.uid
,(count(1)-count(e.submit_time))
,count(e.submit_time)
,group_concat(distinct concat(date_format(e.start_time,'%Y-%m-%d'),':',i.tag) order by e.start_time separator ';') detail
from exam_record e
left join examination_info i on e.exam_id=i.exam_id
where uid in (
select uid
from exam_record
where year(start_time) = 2021
group by uid
having count(score)>=1 and (count(1)-count(score))<5
) and year(e.start_time) = 2021
group by e.uid
having (count(1)-count(e.score))>1
order by e.uid desc
- 解法二
select e.uid
,sum(case when e.submit_time is null then 1 else 0 end) as imcomplete_cnt
,sum(case when e.submit_time is not null then 1 else 0 end) as complete_cnt
,group_concat( distinct concat(date(e.start_time),':',i.tag)
order by e.start_time separator ';') as detail
from exam_record e
left join examination_info i on e.exam_id=i.exam_id
where year(e.start_time)=2021
group by e.uid
having imcomplete_cnt>1 and complete_cnt>=1 and imcomplete_cnt<5
order by imcomplete_cnt desc;
SQL20 月均完成试卷数不小于3的用户爱作答的类别
select i.tag,count(i.tag) tag_cnt
from exam_record e
left join examination_info i on e.exam_id=i.exam_id
where e.uid in (
select uid
from exam_record
where submit_time is not null
group by uid,month(start_time)
having count(submit_time)>=3
) -- and e.submit_time is not null 错误:理解题目意思,只要满足前面条件就可以,后面不需要满足
group by i.tag
order by tag_cnt desc
SQL21 试卷发布当天作答人数和平均分
select
i.exam_id
,count(distinct t.uid) uv
,round(avg(r.score),1) avg_score
from examination_info i left join exam_record r
on i.exam_id=r.exam_id
left join (select uid
from user_info
where level>5) t
on t.uid=r.uid
where date_format(i.release_time,"%Y-%m-%D")=date_format(r.submit_time,"%Y-%m-%D")
and t.uid is not null
group by i.exam_id
order by uv desc,avg_score asc
SQL22 作答试卷得分大于过80的人的用户等级分布
select u.level level,count(level) level_cnt
from examination_info i
left join exam_record r on i.exam_id=r.exam_id
left join user_info u on u.uid = r.uid
where i.tag='SQL' and r.score>80 and u.uid is not null
group by u.level
order by level_cnt desc,level desc
SQL23 每个题目和每份试卷被作答的人数和次数
- 注解
left(str,len)函数:从左边取len个字符
right(str,len)函数从右边取len个字符
select
exam_id tid
,count(distinct uid) uv
,count(1) pv
from exam_record
group by exam_id
union all
select
question_id tid
,count(distinct uid) uv
,count(submit_time) pv
from practice_record
group by question_id
order by left(tid,1) desc,uv desc,pv desc
SQL24 分别满足两个活动的人
- 注解
1、“activity1” as activity这种写法
2、timestampdiff函数中不能是minute而具体到秒才可以
select
uid
,"activity1" as activity
from exam_record
group by uid
having avg(score) >= 85 and min(score)>=85
union all
select
distinct uid
,"activity2" as activity
from exam_record r
left join examination_info i on r.exam_id=i.exam_id
where timestampdiff(second,r.start_time,r.submit_time)<=i.duration * 30 and r.score>=80 and i.difficulty='hard'
order by uid
SQL25 满足条件的用户的试卷完成数和题目练习数
- 注解
注意加上2021年限制条件,排序问题,后面就一直子查询嵌套
select
t1.uid
,t1.exam_cnt
,ifnull(t2.question_cnt,0)
from(
select uid,count(score) exam_cnt
from exam_record
where uid in (
select uid
from exam_record
where uid in (
select uid
from user_info
where level=7
) and exam_id in (
select exam_id
from examination_info
where tag='SQL' and difficulty='hard'
)
group by uid
having avg(score)>80
)and year(submit_time) =2021
group by uid
) t1
left join
(
select uid,count(1) question_cnt
from practice_record
where uid in (
select uid
from exam_record
where uid in (
select uid
from user_info
where level=7
) and exam_id in (
select exam_id
from examination_info
where tag='SQL' and difficulty='hard'
)
group by uid
having avg(score)>80
) and year(submit_time) =2021
group by uid
) t2
on t1.uid=t2.uid
order by exam_cnt asc,right(t1.uid,1) desc;
SQL26 每个6/7级用户活跃情况
select user_info.uid,
count(distinct act_month) as act_month_total,
count(distinct case when year(act_time)='2021'then act_day end) as act_days_2021,
count(distinct case when year(act_time)='2021' and tag='exam' then act_day end) as act_days_2021_exam,
count(distinct case when year(act_time)='2021' and tag='question'then act_day end) as act_days_2021_question
from (SELECT uid,exam_id as ans_id,start_time as act_time,date_format(start_time,'%Y%m') as act_month, date_format(start_time,'%Y%m%d') as act_day,'exam' as tag from exam_record
UNION ALL
select uid,question_id as ans_id,submit_time as act_time,date_format(submit_time,'%Y%m') as act_month, date_format(submit_time,'%Y%m%d') as act_day,'question' as tag from practice_record ) total
right join user_info on total.uid=user_info.uid
where user_info.level in (6,7)
group by total.uid
order by act_month_total desc,act_days_2021 desc
SQL27 每类试卷得分前3名
- 注解
如果查询的其他部分(WHERE,GROUP BY,HAVING)需要窗口函数,
请使用子查询,然后在子查询中在使用窗口函数
1、专用窗口函数,如rank, dense_rank, row_number等专用窗口函数。
2、聚合函数,如sum. avg, count, max, min等
select t1.tag,t1.uid,t1.rk as ranking
from(
select
t.tag
,t.uid
,row_number() over(partition by t.tag order by t.max_score desc,t.min_score desc,t.uid desc) as rk
from(
select
i.tag
,e.uid
,max(e.score) max_score
,min(e.score) min_score
from exam_record e
inner join examination_info i on e.exam_id=i.exam_id
group by i.tag,e.uid
) t
group by t.tag,t.uid
) t1
where rk<=3
SQL28 第二快/慢用时之差大于试卷时长一半的试卷
- 解法一
先将用时时间排序,正序和倒叙两种,然后取出正序第二,倒叙第二,将两张表关联,然后用倒叙第二减正序第二大于试卷时长一半即可,最后根据试卷id倒叙
select
t2.exam_id
,i.duration
,i.release_time
from(
select
exam_id
,max(minute_diff)-min(minute_diff) as diff
from(
select
exam_id
,minute_diff
,row_number() over(partition by exam_id order by minute_diff asc) rk1
,row_number() over(partition by exam_id order by minute_diff desc) rk2
from(
select
exam_id
,timestampdiff(second,start_time,submit_time) as minute_diff
from exam_record
where submit_time is not null
) as t
) t1
where rk1=2 or rk2=2
group by exam_id
) t2
left join examination_info i on i.exam_id=t2.exam_id
where t2.diff >i.duration*30
order by t2.exam_id desc
- 解法二
NTH_VALUE()函数返回expression窗口框架第N行的值。如果第N行不存在,则函数返回NULL。N必须是正整数,例如1,2和3。
select
distinct c.exam_id
,duration
,release_time
from
(select
a.exam_id
,nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) desc ) as low_2
,nth_value(TIMESTAMPDIFF(second,start_time,submit_time),2) over (partition by exam_id order by TIMESTAMPDIFF(minute,start_time,submit_time) asc) as fast_2
,duration
,release_time
from exam_record a
left join examination_info b on a.exam_id = b.exam_id
) c
where low_2-fast_2>duration*30
order by exam_id desc;
SQL29 连续两次作答试卷的最大时间窗
-- 日期相减必须用函数timestampdiff或者datediff,若直接相减会出现保留两位小数的情况
with temp as (
select
uid
,date_day
,lag(date_day,1) over(partition by uid order by date_day) as last_1_time
from (
select
uid
,date_format(start_time,'%Y%m%d') date_day
from exam_record
where uid in (
select uid
from exam_record
group by uid
having count(distinct date_format(start_time,'%Y%m%d'))>=2
)
) t
where year(date_day)=2021
)
select
uid
,max(datediff(date_day,last_1_time))+1 as days_window -- 最大窗口
,round((count(uid)/(datediff(max(date_day),min(date_day))+1))*(max(datediff(date_day,last_1_time))+1),2) as avg_exam_cnt -- 平均答题数
from temp
group by uid
order by days_window desc,avg_exam_cnt desc
SQL30 近三个月未完成试卷数为0的用户完成情况
-- 理解题意,看解释
with temp as(
select
*
,dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as rk
from exam_record
)
select
uid
,count(start_time) as exam_complete_cnt
from temp
where rk <= 3
group by uid
having count(score) = count(id)
order by exam_complete_cnt desc,uid desc
SQL31 未完成率较高的50%用户近三个月答卷情况
with rank_user as (
select
uid
,count(start_time)-count(submit_time) as incomplete_cnt
,count(start_time) as total_cnt
,(count(start_time)-count(submit_time))/count(start_time) incomplete_rate
,row_number() over(order by (count(start_time)-count(submit_time))/count(start_time))as rk
from exam_record
left join examination_info using(exam_id)
where tag='sql'
group by uid
)
select uid,start_month,total_cnt,complete_cnt
from
(
select
uid
,date_format(start_time,'%Y%m') start_month
,row_number() over(partition by uid order by date_format(start_time,'%Y%m') desc) rn
,count(start_time) as total_cnt
,count(submit_time) as complete_cnt
from exam_record
where uid in (
select uid
from(
select
*,rk/(max(rk) over()) as ranking
from rank_user
) t
where ranking>0.5 and uid in(
select uid
from user_info
where level=6 or level=7
)
)
group by uid,start_month
) temp
where rn<=3
order by uid asc,start_month asc
SQL32 试卷完成数同比2020年的增长率及排名变化
-- unsign
-- cast(uid as signed) 类型转换函数,将整型转换成无符号整型
with temp as(
select
tag
,year(start_time) start_year
,count(start_time) exam_cnt
,rank() over(partition by year(start_time) order by count(start_time) desc) exam_cnt_rank
from exam_record
join examination_info using(exam_id)
where submit_time is not null and month(start_time)<=6
group by tag,start_year
)
select
t1.tag as tag
,t1.exam_cnt exam_cnt_20
,t2.exam_cnt exam_cnt_21
,concat(if(cast(t1.exam_cnt as signed)>cast(t2.exam_cnt as signed),'-',''),round(abs(cast(t1.exam_cnt as signed)-cast(t2.exam_cnt as signed))/cast(t1.exam_cnt as signed)*100,1),'%')as growth_rate
,t1.exam_cnt_rank exam_cnt_20
,t2.exam_cnt_rank exam_cnt_21
,cast(t2.exam_cnt_rank as signed) - cast(t1.exam_cnt_rank as signed) rank_delta
from temp t1
join temp t2
on t1.tag=t2.tag and (t1.start_year+1)=t2.start_year
order by growth_rate desc,rank_delta desc
SQL33 对试卷得分做min-max归一化
with temp as(
select
uid
,exam_id
,case
when count(exam_id) over(partition by exam_id)=1 then score
else
(((score-min(score) over(partition by exam_id))/((max(score) over(partition by exam_id))-(min(score) over(partition by exam_id)))))*100
end as min_max
from exam_record
left join examination_info using(exam_id)
where difficulty='hard' and score is not null
)
select
uid
,exam_id
,round(avg(min_max),0) avg_new_score
from temp
group by uid,exam_id
order by exam_id asc,avg_new_score desc;
SQL34 每份试卷每月作答数和截止当月的作答总数。
with temp as (
select
exam_id
,date_format(start_time,'%Y%m') start_month
from exam_record
where start_time is not null
)
select
exam_id
,start_month
,sum(count(start_month)) over(partition by exam_id,start_month) month_cnt
,sum(count(start_month)) over(partition by exam_id order by start_month) cum_exam_cnt
from temp
group by exam_id,start_month
SQL35 每月及截止当月的答题情况
with temp as (
select start_month,uid,mau
from(
-- 最早登录表
select
uid
,min(date_format(start_time,'%Y%m')) start_month
from exam_record
group by uid
) t1 right join (
-- mau
select
date_format(start_time,'%Y%m') start_month
,count(distinct uid) mau
from exam_record
group by date_format(start_time,'%Y%m')
) t2 using(start_month)
)
select
start_month
,mau
,count(uid) month_add_uv
,max(count(uid)) over(order by start_month) max_month_add_uv
,sum(count(uid)) over(order by start_month)cum_sum_uv
from temp
group by start_month;
SQL36 统计有未完成状态的试卷的未完成数和未完成率
select
exam_id
,sum(if(score is null,1,0)) as incomplete_cnt
,round(avg(if(score is null,1,0)),3) as incomplete_rate
from exam_record
group by exam_id
having incomplete_cnt != 0
SQL37 0级用户高难度试卷的平均用时和平均得分
select
uid
,round(avg(if(score is null,0,score))) as avg_score
,round(avg(if(submit_time is null,duration,timestampdiff(minute,start_time,submit_time))),1) as avg_time_took
from exam_record
left join examination_info
using(exam_id)
where difficulty='hard' and uid in (select uid
from user_info
where level =0)
group by uid
SQL38 筛选限定昵称成就值活跃日期的用户
select
i.uid
,i.nick_name
,i.achievement
from user_info i
left join exam_record er on er.uid=i.uid
left join practice_record pr on pr.uid=i.uid
where i.nick_name like "牛客%号" and i.achievement between 1200 and 2500
group by i.uid
having max(date_format(er.start_time,"%Y%m")) = 202109
or max(date_format(pr.submit_time,"%Y%m")) = 202109
SQL39 筛选昵称规则和试卷规则的作答记录
select
uid
,exam_id
,round(avg(score),0) avg_score
from exam_record
where uid in(
select uid
from user_info
where nick_name rlike '^牛客[0-9]+号$' or nick_name rlike '^[0-9]+$'
) and exam_id in (
select exam_id
from examination_info
where tag like 'c%' or tag like 'C%')
group by uid,exam_id
having avg_score is not null
order by uid,avg_score
SQL40 根据指定记录是否存在输出不同情况
- 注解
1、sql语句中的USING($field_name)的用法
SELECT * FROM student LEFT JOIN sc USING(sno)
等同于SELECT * FROM student LEFT JOIN sc ON student.sno=sc.sno
2、EXISTS 运算符用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False。
WITH t_tag_count as (
SELECT uid, `level`,
COUNT(start_time) - COUNT(submit_time) as incomplete_cnt, -- 未完成数
ROUND(
IFNULL(1 - COUNT(submit_time) / COUNT(start_time), 0),
3) as incomplete_rate, -- 此人未完成率
COUNT(start_time) as total_cnt -- 总作答数
FROM exam_record
RIGHT JOIN user_info USING(uid)
GROUP BY uid
)
SELECT uid, incomplete_cnt , incomplete_rate
FROM t_tag_count
WHERE EXISTS (
SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND `level` = 0
UNION ALL
SELECT uid, incomplete_cnt, incomplete_rate
FROM t_tag_count
WHERE NOT EXISTS (
SELECT uid FROM t_tag_count WHERE `level` = 0 AND incomplete_cnt > 2
) AND total_cnt > 0
ORDER BY incomplete_rate;
SQL41 各用户等级的不同得分表现占比
with temp_grade as(
select
uid
,exam_id
,score
,level
,case
when score>=90 then '优'
when score>=75 then '良'
when score>=60 then '中'
else '差'
end as score_grade
,count(level) over(partition by level) as level_count
from exam_record
left join user_info using(uid)
where score is not null
)
select
level
,score_grade
,round(count(*)/level_count,3) as ratio
from temp_grade
group by level,score_grade
order by level desc,ratio desc;
SQL42 注册时间最早的三个人
select uid,nick_name,register_time
from user_info
order by register_time
limit 3
SQL43 注册当天就完成了试卷的名单第三页
select
u.uid
,u.level
,u.register_time
,max(r.score) max_score
from user_info u
join exam_record r on r.uid=u.uid
join examination_info e on e.exam_id=r.exam_id
where u.job='算法' and e.tag='算法' and date_format(u.register_time,'%Y%m')=date_format(r.submit_time,'%Y%m')
group by u.uid
order by max_score desc
limit 6,3
SQL44 修复串列了的记录
select
exam_id
,substring_index(tag,',',1) tag
,substring_index(substring_index(tag,',',2) ,',',-1) difficulty
,substring_index(tag,',',-1) duration
from examination_info
where difficulty ='';
SQL45 对过长的昵称截取处理
select
uid
,if(char_length(nick_name)>13,concat(left(nick_name,10),'...'),nick_name)
from user_info
where char_length(nick_name)>10
SQL46 大小写混乱时的筛选统计
-- 如果转换后tag并没有发生变化,不输出该条结果
-- 所谓tag没有变换是指里面原有的tag没有新增加数据
select
t1.tag
,t2.answer_cnt
from (
select
tag
,count(start_time) as answer_cnt
from examination_info i
join exam_record r on i.exam_id=r.exam_id
group by tag
) t1 join (
select
tag
,count(start_time) as answer_cnt
from examination_info i
join exam_record r on i.exam_id=r.exam_id
group by tag
) t2
on t1.tag!=t2.tag
and upper(t1.tag)=t2.tag
and t1.answer_cnt<3