按在地上摩擦面试官mysql之实战八

1. 这题主要就是条件查询，datediff（date1,date2）两个时间的差

SELECT id, user_id,product_name,status,client_id,date

FROM order_info

WHERE DATEDIFF(date , '2025-10-15') >= 1

AND status = 'completed'

AND product_name IN ('C++' , 'Java' , 'Python')

ORDER BY id;

1. 先统计满足条件的学生数量

2. 再取数量大于2的学生

SELECT user_id

FROM(SELECT user_id,COUNT(user_id) AS u_n FROM order_info WHERE date > "2025-10-15" AND status = "completed" AND product_name IN("C++","Python","Java")

GROUP BY user_id) AS r_order

WHERE u_n >= 2

ORDER BY user_id;

1. 根据上题可以简单的获取到满足条件的用户

2. 将满足条件的用户作为条件

select * from order_info where user_id in

(select user_id from order_info  where date>"2025-10-15"

 and status = "completed" and product_name in ("Python","C++","Java")

 group by user_id having count(user_id)>=2) and  date>"2025-10-15"

and status = "completed" and product_name in ("Python","C++","Java")

1. 查询已购买的日期2025-10-15之后的，课程也满足的

2. 统计满足条件的数量，以及第一次的时间

select user_id,min(`date`) as first_buy_date,count(*) as cnt

from order_info

where `date`>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')

group by user_id

having count(*)>1

order by user_id;

1. 利用窗口函数找到用户user_id和日期

2. 再利用case when 找到第一次和第二次购买的时间

3. 同时一个下两单的人

select user_id,

    min(case when n=1 then date end) as first_buy_date,

    min (case when n=2 then date end) as second_buy_date,

    sum(1) as cnt

from(

    select user_id, date, row_number() over(partition by user_id order by date) as n

    from order_info

    where status = 'completed' and date > '2025-10-15' and product_name in ('C++','Java','Python')

) a

group by user_id

having sum(1) >= 2

1. 先找购买成功对应课程的信息以及数量

2. 关联client表，找到对应name由于团购显示Null，即用left关联

3. 要求下单2个以及以上添加条件过滤

select a.id,is_group_buy,name

from

(select *,count(*)over(partition by user_id) as count_num

from order_info

where product_name in ('C++','Java','Python')

and status='completed'

and date >'2025-10-15') a

left join client b

on a.client_id = b.id

where a.count_num >=2

order by id asc;

1. 依照前面题的思路，很容易写出来

2. 唯一变化的地方就是增加了client表中name进行分组

select

case when A.is_group_buy = "Yes" then "GroupBuy" else B.name end as source,

count(*) as cnt

from

(

    select *

    from

    (

        select is_group_buy, client_id,

        count(*) over(partition by user_id) as number

        from

        order_info

        where product_name in ("C++", "Java", "Python")

        and status = 'completed'

        and date > '2025-10-15'

    ) as t

    where t.number >= 2

) as A

left join

client B

on A.client_id = B.id

group by case when A.is_group_buy = "Yes" then "GroupBuy" else B.name end

order by source;

1. 利用函数year找到对应的年份

2. 统计岗位的数量，排序

SELECT job,SUM(num) AS cnt

FROM resume_info

WHERE year(date) = 2025

GROUP by job

ORDER BY cnt DESC;

1. 利用date_format(date,’%Y-%m’)找到月份

2. 分组求和，排序

SELECT job,DATE_FORMAT(date,'%Y-%m') as mon,sum(num) as cnt

FROM resume_info ri

where DATE_FORMAT(date,'%Y')  = '2025'

group by job,mon

order by mon desc,cnt desc

1. 行转列首先想到关联，同时两个年维度，需要关联两次

2. 分别找到2025年和2026年的数据每个月份的数据

3. 关联起来，同时月份对应相等

select s1.job,date_format(s1.date,'%Y-%m') as first_year_mon,s1.cnt first_year_cnt,

date_format(s2.date,'%Y-%m') second_year_mon,s2.cnt as second_year_cnt

from (select job,date,sum(num) cnt

    from resume_info

    where year(date) ='2025'

    group by job,month(date)) s1

join (select job,date,sum(num) cnt

     from resume_info

     where year(date) = '2026'

      group by job,month(date)) s2

on s1.job = s2.job

and month(s1.date) = month(s2.date)

order by first_year_mon desc,job desc