刷牛客的第五天2024.1.28

组合查询

VQ20 查询职位城市在北京或者职位工资高于100000的job_id和company_id，结果不去重

select job_id,company_id
from job_info
where job_city like "%北京%" 
union all
select job_id,company_id
from job_info
where salary>100000

不查重查询，union all可以用，不能用or

---

VQ21 查询职位发布时间在2021年后或职位城市为上海的job_id, boss_id, company_id

select job_id,boss_id,company_id
from job_info
where job_city like"%上海%" or year(post_time) >=2021
order by job_city

这个题目不需要查重，直接用or'就可以进行查询筛选。

---

技能专项-case when使用

VQ22 判断其是否有过购买记录

select customer_id,
if(latest_place_order_date is null,0,1) as if_placed_order
from customers_info

用if语句来判断是否为空

---

VQ23 请按城市对客户进行排序,如果城市为空，则按国家排序

select *
from customers_info
order by (case when city is not null then city else country end)

用case when 语句来进行排序的顺序

---

VQ24 分群并计算群体人数

select  ( case
    when age<20 then '20以下'
    when age>=20 and age<=50 then '20-50'
    when age>=50 then '50以上'
    else '未填写' end  
 ) as age_group ,
count(customer_id) as user_count
from customers_info
group by age_group

用case when 来对他进行值的分段，count数的是id并不是age 因为在表中age有空的，customer_id是表的主键，不会落下。

---

技能专项-窗口函数

VQ25 查询每天刷题通过数最多的前二名用户id和刷题数

SELECT
    date,
    user_id,
    pass_count
FROM
    (
        SELECT
            user_id,
            pass_count,
            date,
            row_number() over (
                PARTITION BY
                    date
                ORDER BY
                    pass_count DESC
            ) rk
        FROM
            questions_pass_record
    ) AS t1
WHERE
    rk <= 2

这个先搞出一个表把排序给写在后面，然后在用where rk《=2来把他筛选出来。

---

VQ26 查询用户刷题日期和下一次刷题日期

select
    user_id,
    date,
    ifnull (
        lead (date) over (
            partition by
                user_id
            order by
                date
        ),
        'None'
    ) as nextdate
from
    questions_pass_record

LEAD()函数：用于获取指定列的当前行之后的一行数据

IFNULL()函数：ifnull(A,B) 如果A有值，返回A的值；如果A是空，返回B

这两个窗口函数。

---

技能专项-having字句

VQ27 输出提交次数大于2次的用户ID且倒序排列

select distinct
 qpd.user_id as user_id 
from (
    select 
    user_id,
    count(done_time) as numbers
    from 
    done_questions_record
    group by user_id
    having 
     numbers>2
    ) as qpd
    order by
     qpd.user_id desc

having语句

HAVING是在 GROUP BY分组之后进行条件筛选的，后面可以跟聚合函数

---

VQ28 输出提交且通过次数大于2 的用户ID且升序排列

select user_id
from done_questions_record 
where result_info =1
group by user_id 
having count(*) >2
order by user_id asc

脑瓜子昏涨涨的，睡觉去了