文章目录
知识题目1常规解法1:解法2:解法3:解法4:解法5(利用专用窗口函数:rank、dense_rank、row_number):题目2解1:用if条件函数解2:在聚合函数中添加条件语句解3:用case when...then...end题目3:解1:聚合函数求出平均值,作为表中的一个筛选条件解2:解1的另一种写法解3:用窗口函数直接求出avg,作为一列。然后score与avg比较解4:解3的另写(在部分字段前加上了表名)解5:错误写法:报错"Table 't' doesn't exist"题目4:解1:解2:子查询解3:基于解2知识
聚合函数:max、min、count、avg、sum
分组函数:group by
聚合函数会自动忽略值为null的行聚合函数只能直接加到SELECT, HAVING, GROUP BY
后面
题目1
/practice/a690f76a718242fd80757115d305be45?tpId=240&tqId=2180959&ru=/ta/sql-advanced&qru=/ta/sql-advanced/question-ranking
常规解法1:
select tag, difficulty, round( (sum(score)-max(score)-min(score)) / (count(score)-2) ,1) as clip_avg_scorefrom examination_info as t1inner joinexam_record as t2on t1.exam_id=t2.exam_idwheretag="SQL" and difficulty="hard";
解法2:
select tag, difficulty, round( (sum(score)-max(score)-min(score)) / (count(score)-2) ,1) as clip_avg_scorefrom examination_infojoin # 默认是inner joinexam_record using(exam_id)wheretag="SQL" and difficulty="hard";
注意:
using
只能在相同字段名的判等使用。
解法3:
select tag, difficulty, round(avg(score), 1) as clip_avg_scorefrom exam_record as t1left join # inner join 也对 examination_info as t2 using(exam_id)wheretag="SQL" and difficulty="hard"and score not in (select max(score) from exam_record)and score not in (select min(score) from exam_record);
注意:
例如最后的score not in (select max(score) from exam_record)
不能写为score is not max(score)
是因为聚合函数只能直接加到SELECT, HAVING, GROUP BY
后面,因此要写一个子查询,先把最大、最小的score查找出来。例如最后的score not in (select max(score) from exam_record)
的表名只能是原表名,不能是t1。否则报错:“Table ‘t1’ doesn’t exist”。
解法4:
select tag, difficulty, round(avg(score), 1) as clip_avg_scorefrom exam_record as t1left join # inner join 也对 examination_info as t2 using(exam_id)wheretag="SQL" and difficulty="hard"and score < (select max(score) from exam_record) and score > (select min(score) from exam_record)
解法5(利用专用窗口函数:rank、dense_rank、row_number):
select tag, difficulty, round(avg(score), 1) as clip_avg_scorefrom(select-- exam_record.id ,exam_id, tag, difficulty, score, exam_id, tag, difficulty, score, # 这儿的注意点见下面dense_rank() over(order by score asc) as score_asc_ranking,dense_rank() over(order by score desc) as score_desc_rankingfrom exam_record left join examination_info using(exam_id)where tag="SQL" and difficulty="hard" and score is not null) AS twhere t.score_asc_ranking!=1 and t.score_desc_ranking!=1group bytag;
注意:
exam_id, tag, difficulty, score,
1.若写为*
,会报错:“Duplicate column name ‘id’”,因为id列是二表都有的,而虽然exam_id也是二表都有的,但它是连结字段,因此可以直接写exam_id。
2.若写为id ,exam_id, tag, difficulty, score,
,会报错:“Column ‘id’ in field list is ambiguous”,跟上面1.
的报错原理其实一样。
3.若写为exam_record.id ,exam_id, tag, difficulty, score,
,即将id指定表->exam_record.id,就OK。
题目2
/practice/45a87639110841b6950ef6a12d5f?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
解1:用if条件函数
SELECTcount(start_time) as total_pv,count(submit_time) as complete_pv,count(distinct if(submit_time is not null, exam_id, NULL)) as complete_exam_cntFROMexam_record;
解2:在聚合函数中添加条件语句
SELECTcount(start_time) as total_pv,count(submit_time) as complete_pv,count(DISTINCT exam_id and score IS NOT NULL) as complete_exam_cnt,FROMexam_record;
要注意count(DISTINCT exam_id and score IS NOT NULL)
,聚合函数中也可以添加条件语句.
解3:用case when…then…end
SELECTcount(start_time) as total_pv,count(submit_time) as complete_pv,#count(distinct case when submit_time is not null then exam_id else null end ) as complete_exam_cntcount(distinct case when score is not null then exam_id else null end ) as complete_exam_cntFROMexam_record;
题目3:
/practice/3de23f1204694e74b7deef08922805b2?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0
解1:聚合函数求出平均值,作为表中的一个筛选条件
select min(score) as min_score_over_avgFROM exam_record as erjoinexamination_info as eion er.exam_id=ei.exam_idwhere tag="SQL" andscore>=(select avg(score) from exam_record er join examination_info ei on er.exam_id=ei.exam_idwhere tag='SQL')
解2:解1的另一种写法
select min(score) as min_score_over_avgFROM exam_record joinexamination_info using(exam_id)where tag="SQL" andscore>=(select avg(score) from exam_record er join examination_info ei on er.exam_id=ei.exam_idwhere tag='SQL')
解3:用窗口函数直接求出avg,作为一列。然后score与avg比较
selectmin(t.score) as min_score_over_avg from(selectscore,avg(score) over() as avg_score from #exam_record #inner join # left#examination_info #using(exam_id)exam_record as erleft joinexamination_info as eion er.exam_id=ei.exam_idwheretag="SQL" andscore is not null) as twhere t.score>=t.avg_score;
解4:解3的另写(在部分字段前加上了表名)
selectmin(t.score) as min_score_over_avg from(selecter.score,avg(er.score) over() as avg_score from exam_record as erleft joinexamination_info as eion er.exam_id=ei.exam_idwhereei.tag="SQL" ander.score is not null) as twhere t.score>=t.avg_score;
解5:错误写法:报错"Table ‘t’ doesn’t exist"
目前咱不知道为啥报错。。。
select min(t.score) as min_score_over_avgFROM (select scorefrom exam_record inner join # inner/left join examination_info using(exam_id) where tag="SQL" and score is not null) as twhere t.score>=(select avg(t.score) from t);
题目4:
/practice/9e2fb674b58b4f60ac765b7a37dde1b9?tpId=240&tqId=2183005&ru=/practice/3de23f1204694e74b7deef08922805b2&qru=/ta/sql-advanced/question-ranking
解1:
SELECTDATE_FORMAT(submit_time, "%Y%m") as month,round((count(distinct uid, DATE_FORMAT(submit_time, "%y%m%d"))) / count(distinct uid), 2) as avg_active_days,COUNT(distinct uid) as mauFROMexam_recordWHEREsubmit_time is not NULLand year(submit_time)=GROUP BYDATE_FORMAT(submit_time, "%Y%m")#month; 也ok
解2:子查询
selectSUBSTR(ymd, 1, 6) as month,round(count(1) / count(distinct uid), 2) as avg_active_days,count(distinct uid) as mauFROM(SELECT DISTINCT uid, DATE_FORMAT(submit_time, "%Y%m%d") as ymdfrom exam_recordwhere submit_time is not NULL and YEAR(submit_time)= # ""也OK) as tGROUP BYSUBSTR(ymd, 1, 6);#month; 也ok
注意:
count(1)就相当于count(*),对总行数计数,其效率更高。FROM(…) as t,必须有as t,否则报错: ‘Every derived table must have its own alias’–“每个派生表必须有自己的别名”。
解3:基于解2
selectt.Ym as month,round(count(*) / count(distinct t.uid), 2) as avg_active_days,count(distinct t.uid) as mauFROM(SELECT DISTINCT uid, DATE_FORMAT(submit_time, "%Y%m") as Ym,DATE_FORMAT(submit_time, "%Y%m%d") as ymdfrom exam_recordwhere submit_time is not NULL and YEAR(submit_time)= # ""也OK) as tGROUP BYt.Ym;
或:
selectYm as month,round(count(*) / count(distinct uid), 2) as avg_active_days,count(distinct uid) as mauFROM(SELECT DISTINCT uid, DATE_FORMAT(submit_time, "%Y%m") as Ym,DATE_FORMAT(submit_time, "%Y%m%d") as ymdfrom exam_recordwhere submit_time is not NULL and YEAR(submit_time)= # ""也OK) as tGROUP BYYm;