牛客网-SQL-大厂笔试真题刷题31-39题记录&解析-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_52375817/article/details/145616176

牛客网在线编程_SQL篇_SQL大厂笔试真题

SQL31 : 完成员工考核试卷突出的非领导员工_牛客题霸_牛客网

/* 第一种思路没做出来 有时间多做几遍
WITH T0 AS(
-- STEP 1: 3张表连接，取字段，求作答时间&个人平均分数&求同类试卷总体平均值
SELECT 
    emp_info.emp_id,
    examination_info .exam_id,
    emp_level,
    tag as exam_tag,
    -- 求出作答时间 按照秒->转化为分钟 
    TIMESTAMPDIFF(SECOND,start_time,submit_time) / 60 as duration_time,
    score,
    -- 求 个人平均分数 
    AVG(score) OVER(PARTITION BY emp_id ) as avg_emp_socre,
    -- 求同类试卷总体平均值
    AVG(score) OVER(PARTITION BY exam_id) as avg_examid_socre
FROM emp_info 
LEFT JOIN exam_record  
ON emp_info.emp_id = exam_record.emp_id
LEFT JOIN examination_info
ON examination_info.exam_id=exam_record.exam_id
WHERE emp_level<7 -- 等级小于7的员工
) 
-- step3 :
SELECT 
    emp_id,
    emp_level,
    exam_tag
FROM(SELECT  *,
    AVG(duration_time) OVER(PARTITION BY exam_id) as avg_examid_time-- 求每个试卷的平均作答时间
FROM T0 
WHERE avg_examid_socre < avg_emp_socre -- 个人平均分数大于同类试卷总体平均值
) AS T 
WHERE duration_time<avg_examid_time -- 作答用时少于同类试卷平均值
ORDER BY emp_id, exam_id
*/

with
    t as (
        select
            exam_id,
            avg(score) avg_score,
            avg(timestampdiff (second, start_time, submit_time)) avg_time
        from
            exam_record
        group by
            exam_id
    )
select
    t1.emp_id emp_id,
    emp_level,
    tag exam_tag
from
    (select * from exam_record left join emp_info using (emp_id) left join examination_info using(exam_id)) t1
    left join t using (exam_id)
where
    score > avg_score
    and timestampdiff (second, start_time, submit_time) < avg_time
    and t1.emp_level<7
order by emp_id,exam_id

以上是 2025-2-13 做的，做的不太好，下面是2025-2-14 早做的，可以参考下面的代码。

如果有很多限制条件，特别是遇到出现“平均”等需要分组的，首先考虑这些麻烦字段可以通过哪些表实现，如果有的表一开始就可以处理这些，可以先只通过这张表实现。后续再表连接，否则表连接太多了，容易出错（个人小Tip)

-- STEP1 :根据题意，需要算出  以下4个 都可以通过exam-record表先计算出 
-- 1、每个人的每一个考试的 作答用时 ，新加一列 
-- 2、同类试卷平均值 作答用时
-- 3、个人分数
-- 4、同类试卷总体平均值 
WITH T0 AS (
    SELECT 
        emp_id,exam_id,
        TIMESTAMPDIFF(SECOND,start_time,submit_time) / 60 as duration_time, -- 1、每个人的每一个考试的 作答用时 ，新加一列 
        AVG( TIMESTAMPDIFF(SECOND,start_time,submit_time) / 60 ) OVER(PARTITION BY exam_id) as avg_examid_time,         -- 2、每个试卷的 平均作答用时
        score ,  -- 3、个人分数
        AVG(score) OVER(PARTITION BY exam_id ) as avg_examid_score   -- 4、同类试卷总体平均值 
    FROM exam_record 
)
-- STEP 2：3表连接，筛选条件 
SELECT 
    -- 选出需要的字段 
    T0.emp_id,
    emp_level,
    tag as exam_tag
FROM emp_info JOIN T0 
ON T0.emp_id=emp_info.emp_id
JOIN examination_info 
ON T0.exam_id = examination_info.exam_id
WHERE emp_info.EMP_LEVEL<7 -- 员工信息表 emp_info 条件 员工等级小于7
AND score > avg_examid_score -- 筛选个人分数> 试卷平均得分 eg: 9001试卷平均得分为78.667分，得分高于78.667
AND  duration_time < avg_examid_time -- 筛选个人答题时间< 试卷平均答题时间 eg:试卷平均作答时间为27.3分钟，作答时间小于27.3分钟
ORDER BY emp_id ASC, T0.exam_id ASC

SQL32:查询产生理赔费用的快递信息_牛客题霸_牛客网

SELECT 
    express_tb.exp_number,
    exp_type,
    claims_cost 
FROM express_tb JOIN exp_cost_tb
ON express_tb.exp_number = exp_cost_tb.exp_number
WHERE  claims_cost IS NOT NULL -- 筛选出有理赔费用的快递
ORDER BY claims_cost DESC

SQL33:统计快递运输时长_牛客题霸_牛客网

-- STEP2：分组 求平均间隔时长 排序 
SELECT 
    exp_type,
    ROUND(AVG(stamptime),1) as time
FROM(
-- STEP1: 表连接& 求间隔时长
SELECT 
    express_tb.exp_number,
    exp_type,
    TIMESTAMPDIFF(SECOND,out_time,in_time) / 3600 AS stamptime -- 利用日期函数求出间隔时长 涉及到求两个时间段相减，使用 TIMESTAMPDIFF(second)/3600 化为h
FROM express_tb JOIN exp_action_tb
ON express_tb.exp_number = exp_action_tb.exp_number) AS T1 
GROUP BY exp_type
ORDER BY time

SQL34:统计快递从创建订单到发出间隔时长_牛客题霸_牛客网

SELECT 
    ROUND(AVG(timestamp),3) AS time 
FROM(

SELECT 
    express_tb.exp_number,
    TIMESTAMPDIFF(MINUTE,create_time,out_time) / 60 as timestamp 
FROM express_tb JOIN  exp_action_tb
ON express_tb.exp_number= exp_action_tb.exp_number) as t

SQL35:下单最多的商品_牛客题霸_牛客网

select a.product_id,count(a.product_id) cnt
from user_client_log a
join product_info b using(product_id)
where step='order'
group by a.product_id
order by cnt desc,a.product_id
limit 1

SQL36:用户购买次数前三_牛客题霸_牛客网

# 没有用窗口函数
select uid,count(uid) cnt
from user_client_log 
where step='order'
group by uid
order by cnt desc
limit 3

SQL37:商品价格排名_牛客题霸_牛客网

SELECT 
    product_id,
    product_name,
    type,
    price
FROM (SELECT 
    product_id,
    product_name,
    type,
    price,
    -- ROW_NUMBER() over(partition by type ORDER BY price DESC) as rk -- 试了下输出 不对 有相同价格 
    RANK() over(partition by type ORDER BY price DESC) as rk -- 试了下输出 不对 有相同价格 
FROM product_info) as t 
WHERE rk<=2 
ORDER BY price desc,product_name
LIMIT 3

SQL38:商品销售排名_牛客题霸_牛客网

    -- 根据商品名称分组聚合统计销售额，最后以销售额进行排名，返回前两个商品的销售额度和销售额
    -- GROUP BY product_name ,ORDER BY LIMIT2 
    -- 只有select步骤的数据有pay_method字段； 如果select中pay_method为''则以error标记pay_method为脏数据；无需考虑返回为空的异常处理
    -- IF(pay_method = "",'error',pay_mehod) as pay 


SELECT 
    product_name,
    -- sum(price) as total_price 
    count(distinct trace_id) * price as total_price
FROM user_client_log 
JOIN product_info
ON user_client_log.product_id = product_info.product_id
WHERE user_client_log.step='select' -- user_client_log表中有多条数据，取出一条  或者可以等价于\
# where pay_method <> ''
GROUP BY product_name,price -- 如果select count()*price 涉及到了price，则GROUP BY 也需要有
ORDER BY total_price DESC
LIMIT 2

SQL39:商品销售总额分布_牛客题霸_牛客网

SELECT 
    -- pay_method,-- 只有select步骤的数据有pay_method字段；如果select中pay_method为''则以error标记pay_method为脏数据；
    (CASE WHEN pay_method ='' THEN 'error' ELSE pay_method END) as pay,
    Count(trace_id) as cnt
FROM user_client_log JOIN product_info
ON product_info.product_id=user_client_log.product_id
   AND product_name='anta' 
WHERE step ='select' -- 只有select步骤的数据有pay_method字段
GROUP BY pay_method -- 按照支付方式 求和
ORDER BY cnt DESC