电商设计师联盟网站,网站添加新关键词,中学建设校园网站方案,网站必须做商标么文章目录 一、基本查询二、数据过滤三#xff1a;函数四#xff1a;分组聚合五#xff1a;子查询六#xff1a;多表连接七#xff1a;组合查询八#xff1a;技能专项-case when使用九#xff1a;多表连接-窗口函数十#xff1a;技能专项-having子句十一#xff1a;技能… 文章目录 一、基本查询二、数据过滤三函数四分组聚合五子查询六多表连接七组合查询八技能专项-case when使用九多表连接-窗口函数十技能专项-having子句十一技能专项-一网打尽时间函数十二技能专项-一网打尽字符函数 一、基本查询
【题目1查询所有投递用户user id并去重】
select user_id
from deliver_record
group by user_id
;【题目2查询限制行数】
select user_id,job_id,device,job_salary,deliver_date
from deliver_record
limit 2
;【题目3将查询列重新命名】
select job_salary 职位工资
from deliver_record
;【题目4查询表总行数】
select count(*) cnt
from deliver_record
;二、数据过滤
【题目1查询在pc上投递的所有投递记录】
select * from deliver_record_detail
where devicepc
;【题目2查询投递最低最高薪资差别大于2的职位的投递用户】
select user_id
from deliver_record_detail
where (max_salary-min_salary)2
; 【题目3**查询薪资信息不为空的职位投递记录】
select * from deliver_record_detail
where min_salary is not null or max_salary is not null
;【题目4查询城市为北京的职位投递记录】
select * from deliver_record_detail
where job_city like 北京%
;三函数
【题目1计算总刷题数,并将所选列名改为总刷题数】
selectsum(pass_count) 总刷题数
from questions_pass_record_detail
;【题目2计算刷题总人数】
selectcount(distinct user_id) cnt
from questions_pass_record_detail
;【题目3找出sql类题目的单次最大刷题数】
select max(pass_count) maxCnt
from questions_pass_record_detail
where question_typesql
;【题目4计算单次平均刷题数】
select avg(pass_count) avgCnt
from questions_pass_record_detail
;四分组聚合
【题目1统计每天总刷题数】
select date days ,sum(pass_count) passCnt
from questions_pass_record_detail
group by date
;【题目2统计每天刷题数超过5的user id以及刷题数】
select date ,user_id,sum(pass_count) total_pass_count
from questions_pass_record_detail
where pass_count5
group by date,user_id
;【题目3统计不同类型题目的刷题数并按刷题数进行升序排列】
select question_type,sum(pass_count) passCnt
from questions_pass_record_detail
group by question_type
order by passCnt
;五子查询
【题目1查询2022年毕业用户的刷题记录】
select user_id,question_type,device,pass_count,date
from questions_pass_record
where user_id in (select user_idfrom user_infowhere graduation_year2022)
;【题目2查询2022年以来刷题用户的用user id和毕业院校】
select user_id,university
from user_info
where user_id in (select user_idfrom questions_pass_recordwhere substr(date,1,4)2022group by user_id)
;六多表连接
【题目1查询被投递过的职位信息答案有问题】
# 能过的答案
select * from job_info;# 根据题意解
select a.company_id,count(distinct b.user_id) cnt
from (select job_id,company_idfrom job_info
) a join (select user_id,job_id,resume_if_checkedfrom deliver_recordwhere resume_if_checked1
) b on a.job_idb.job_id
group by a.company_id
order by company_id
;【题目2查询每个公司查看过的投递用户数】
select a.company_id,count(distinct b.user_id) cnt
from (select job_id,company_idfrom job_info
) a join (select user_id,job_id,resume_if_checkedfrom deliver_recordwhere resume_if_checked1
) b on a.job_idb.job_id
group by a.company_id
;七组合查询
【题目1查询职位城市在北京或者职位工资高于100000的job_id和company_id】
select job_id,company_id
from job_info
where job_city北京
union all
select job_id,company_id
from job_info
where salary100000
;【题目2查询职位发布时间在2021年后或职位城市为上海的job_id, boss_id, company_id】
select job_id,boss_id,company_id
from (select job_id,boss_id,company_id,job_cityfrom job_infowhere substr(post_time,1,4)2021union select job_id,boss_id,company_id,job_cityfrom job_infowhere job_city上海
) a
order by job_city
;八技能专项-case when使用
【题目1判断其是否有过购买记录】
select customer_id,if(latest_place_order_date is not null,1,0) if_placed_order
from customers_info
;【题目2请按城市对客户进行排序,如果城市为空则按国家排序】
select customer_id,gender,city,country,age,latest_place_order_date
from customers_info
order by city,country
;【题目3分群并计算群体人数】
select age_group,count(customer_id) user_count
from (select case when age20 then 20以下 when age between 20 and 50 then 20-50when age50 then 50以上 when age is null then 未填写else 其他 end age_group,customer_idfrom customers_info
) a
group by age_group
;九多表连接-窗口函数
【题目1查询每天刷题通过数最多的前二名用户id和刷题数】
select date,user_id,pass_count
from (select date,user_id,pass_count,row_number() over(partition by date order by pass_count desc) rnfrom questions_pass_record
) a
where rn2
order by date
;【题目2查询用户刷题日期和下一次刷题日期】
select user_id,date,lead(date,1,None) over(partition by user_id order by date) nextdate
from questions_pass_record
order by user_id,date
;十技能专项-having子句
【题目1输出提交次数大于2次的用户ID且倒序排列】
selectuser_id
from done_questions_record
group by user_id
having count(1)2
order by user_id desc
;【题目2输出提交且通过次数大于2 的用户ID且升序排列】
selectuser_id
from done_questions_record
where result_info1
group by user_id
having count(1)2
;【题目3**验证刷题效果输出题目真实通过率】 question_pass_rate题目通过率通过题目总数(去重)/总题目数(去重) pass_rate提交通过正确率通过题目总数(不去重)/提交次数(不去重) question_per_cnt每题目平均提交次数总提交次数(不去重)/总题目数(去重)
select user_id,count(distinct if(result_info1,question_id,null))/count(distinct question_id) question_pass_rate,sum(result_info)/count(done_time) pass_rate,count(done_time)/count(distinct question_id) question_per_cnt
from done_questions_record
group by user_id
having question_pass_rate0.6
order by user_id
;十一技能专项-一网打尽时间函数
【题目1**广告点击的高峰期】
select click_hour,click_cnt
from(select hour(click_time) click_hour,count(trace_id) click_cnt,row_number() over(order by count(trace_id) desc) rn from user_ad_click_timegroup by hour(click_time)
) a
where rn1
;【题目2**输出在5min内完成点击购买的用户ID】
select a.user_id uid
from user_ad_click_time a
left join user_payment_time b
on a.user_idb.user_id and a.trace_idb.trace_id
where timestampdiff(minute,click_time,pay_time)5
order by a.user_id desc
;十二技能专项-一网打尽字符函数
【题目1字符函数正则匹配1答案有问题】
# 能过的答案
select id,comment
from comment_detail
where comment like %是% or (comment like %试%)
order by id
;# 根据题意解
select id,comment
from comment_detail
where comment like 是% or comment like 求%
order by id
;【题目2字符函数正则匹配2】
select id,comment
from comment_detail
where comment like 是% or comment like 求%
;【题目3话题的分布情况】
select substring_index(subject_set,,,1) subject_id1,count(1) cnt
from comment_detail
where substring_index(substring_index(subject_set,,,2),,,-1)1002
group by substring_index(subject_set,,,1)
order by subject_id1
;【题目4**评论替换和去除】
select id,replace(comment,,) comment
from comment_detail
where length(replace(comment,,))3由于题目说的是汉字长度大于3所以这里就要使用char_length()而不是length(); char_length()单位为字符不管汉字还是数字或者是字母都算是一个字符。 length() 单位是字节utf8编码下,一个汉字三个字节一个数字或字母一个字节。gbk编码下,一个汉字两个字节一个数字或字母一个字节。
