刷牛客的第四天2024.1.27

之前刷的都是sql的非技术快速入门，把落下的知识稍微补充了一下，现在刷的是sql基础进阶，每天都坚持写几道，积少成多，慢慢累计！

基础查询

VQ1 查询所有投递用户user_id并去重

select distinct user_id
from deliver_record

没有了输出实例难度就会增加。

---

 VQ2 查询限制行数

select *
from deliver_record
where job_id='14550'

前两行的job_id都为14550，用where来筛选出就可以了

---

VQ3 将查询列重新命名

select job_salary as "职位工资"
from deliver_record

用as来进行只为工作。

---

VQ4 查询表总行数

select count(*) as cnt
from deliver_record

count(*)查询全部

---

数据过滤

VQ5 查询在pc上投递的所有投递记录

select *
from deliver_record_detail
where device="pc"

---

VQ6 查询投递最低最高薪资差别大于2的职位的投递用户user_id

select user_id
from deliver_record_detail
where max_salary-min_salary>2

---

VQ7 查询薪资信息不为空的职位投递记录

select *
from deliver_record_detail
where min_salary!="null" or max_salary!="null"

注意null要加引号。

---

VQ8 查询城市为北京的职位投递记录

select *
from deliver_record_detail
where job_city like "%北京%"

用like 北京加引号和%

---

函数

VQ9 计算总刷题数,并将所选列名改为总刷题数

select sum(pass_count) as "总刷题数”
from questions_pass_record_detail

---

VQ10 计算刷题总人数

select count(distinct user_id) as 'cnt'
from questions_pass_record_detail

---

VQ11 找出sql类题目的单次最大刷题数

select max(pass_count) as 'max(pass_count)'
from questions_pass_record_detail
where question_type = 'sql'

---

VQ12 计算单次平均刷题数

select avg(pass_count) as 'avgCnt'
from questions_pass_record_detail

---

分组聚合

VQ13 统计每天总刷题数

select date as 'days',
sum(pass_count) as 'passCnt'
from questions_pass_record_detail
group by days

---

VQ14 统计每天刷题数超过5的user_id以及刷题数

select date,
user_id,
pass_count as 'total_pass_count'
from questions_pass_record_detail
where pass_count>5

---

VQ15 统计不同类型题目的刷题数，并按刷题数进行升序排列

select question_type,
sum(pass_count) as 'passCnt'
from questions_pass_record_detail
group by question_type 
order by passCnt asc

---

子查询

VQ16 查询2022年毕业用户的刷题记录

select q.user_id,q.question_type,q.device,q.pass_count,q.date
from questions_pass_record q
left join user_info u
on q.user_id =u.user_id
where u.graduation_year ='2022'

---

VQ17 查询2022年以来刷题用户的用user_id和毕业院校

select distinct u.user_id,u.university
from questions_pass_record q
left join user_info u
on q.user_id =u.user_id
where u.graduation_year<2022

---

多表链接

VQ18 查询被投递过的职位信息

select

  ji.job_id,

  ji.boss_id,

  ji.company_id,

  ji.post_time,

  ji.salary,

  ji.job_city

from

  job_info ji

  join deliver_record dr on ji.job_id = dr.job_id

order by

  ji.boss_id

---

VQ19 查询每个公司查看过的投递用户数

SELECT j.company_id, SUM(d.resume_if_checked) AS cnt
FROM job_info AS j
LEFT JOIN deliver_record AS d
ON j.job_id = d.job_id
WHERE d.resume_if_checked > 0
GROUP BY j.company_id
ORDER BY j.company_id