🎯 SQL面试题大全 - 从基础到高级的完整题库

📊 面试题分级体系

🟢 初级题（1-2年经验）

• ORDER BY排序
• GROUP BY分组
• JOIN连接
• Presto/JOIN优化与子查询

🟡 中级题（2-5年经验）

• JOIN连接
• Hive/窗口函数与
• 数据处理：CASE WHEN、字符串函数、日期函数
• 术语索引表

🔴 高级题（5年以上经验）

• 现代SQL特性与数据库差异
• 业务建模：用户行为分析、漏斗分析
• 系统设计：分库分表、数据仓库
• Oracle/Oracle索引设计与优化

🟢 初级面试题

题目1：查找薪资最高的员工 ⭐

题目描述： 有员工表employees(id, name, salary, department_id)，查找薪资最高的员工姓名。

解题思路：

1. 先找到最高薪资
2. 再查找拥有最高薪资的员工

SELECT name 
FROM employees 
WHERE salary = (SELECT MAX(salary) FROM employees);

SELECT name
FROM (
    SELECT name, 
           RANK() OVER (ORDER BY salary DESC) as rk
    FROM employees
) t 
WHERE rk = 1;

题目2：统计每个部门的平均薪资 ⭐

题目描述： 统计每个部门的员工数量和平均薪资，按平均薪资降序排列。

SELECT 
    d.department_name,
    COUNT(e.id) as employee_count,
    ROUND(AVG(e.salary), 2) as avg_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.department_name
ORDER BY avg_salary DESC;

题目3：查找重复数据 ⭐⭐

题目描述： 查找users表中手机号重复的所有用户记录。

-- 方法一：使用窗口函数
SELECT id, name, phone
FROM (
    SELECT id, name, phone,
           COUNT(*) OVER (PARTITION BY phone) as phone_count
    FROM users
) t
WHERE phone_count > 1
ORDER BY phone, id;

-- 方法二：使用GROUP BY
SELECT u.*
FROM users u
INNER JOIN (
    SELECT phone
    FROM users
    GROUP BY phone
    HAVING COUNT(*) > 1
) dup ON u.phone = dup.phone
ORDER BY u.phone, u.id;

🟡 中级面试题

题目4：连续登录用户分析 ⭐⭐⭐

题目描述： 有用户登录记录表login_logs(user_id, login_date)，查找连续登录超过3天的用户。

解题思路： 使用窗口函数和日期差值来识别连续登录。

WITH login_with_rn AS (
    SELECT 
        user_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) as rn
    FROM (
        SELECT DISTINCT user_id, login_date 
        FROM login_logs
    ) t
),
login_groups AS (
    SELECT 
        user_id,
        login_date,
        DATE_SUB(login_date, INTERVAL rn DAY) as group_date
    FROM login_with_rn
),
consecutive_counts AS (
    SELECT 
        user_id,
        group_date,
        COUNT(*) as consecutive_days,
        MIN(login_date) as start_date,
        MAX(login_date) as end_date
    FROM login_groups
    GROUP BY user_id, group_date
)
SELECT 
    user_id,
    consecutive_days,
    start_date,
    end_date
FROM consecutive_counts
WHERE consecutive_days > 3
ORDER BY user_id, start_date;

题目5：用户留存率分析 ⭐⭐⭐

题目描述： 分析新用户的留存率，计算注册后1天、7天、30天的留存率。

WITH new_users AS (
    SELECT 
        user_id,
        MIN(DATE(created_at)) as registration_date
    FROM users
    GROUP BY user_id
),
user_activity AS (
    SELECT DISTINCT
        user_id,
        DATE(activity_date) as activity_date
    FROM user_activities
),
retention_analysis AS (
    SELECT 
        nu.registration_date,
        nu.user_id,
        -- 1日留存
        MAX(CASE WHEN ua.activity_date = DATE_ADD(nu.registration_date, INTERVAL 1 DAY) 
                 THEN 1 ELSE 0 END) as day1_retained,
        -- 7日留存
        MAX(CASE WHEN ua.activity_date = DATE_ADD(nu.registration_date, INTERVAL 7 DAY)
                 THEN 1 ELSE 0 END) as day7_retained,
        -- 30日留存
        MAX(CASE WHEN ua.activity_date = DATE_ADD(nu.registration_date, INTERVAL 30 DAY)
                 THEN 1 ELSE 0 END) as day30_retained
    FROM new_users nu
    LEFT JOIN user_activity ua ON nu.user_id = ua.user_id
        AND ua.activity_date >= nu.registration_date
    GROUP BY nu.registration_date, nu.user_id
)
SELECT 
    registration_date,
    COUNT(*) as new_users,
    SUM(day1_retained) as day1_retained_users,
    SUM(day7_retained) as day7_retained_users,
    SUM(day30_retained) as day30_retained_users,
    ROUND(AVG(day1_retained) * 100, 2) as day1_retention_rate,
    ROUND(AVG(day7_retained) * 100, 2) as day7_retention_rate,
    ROUND(AVG(day30_retained) * 100, 2) as day30_retention_rate
FROM retention_analysis
GROUP BY registration_date
ORDER BY registration_date;

题目6：销售同比环比增长 ⭐⭐⭐

题目描述： 计算每月销售额的同比增长率和环比增长率。

WITH monthly_sales AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') as sales_month,
        SUM(total_amount) as monthly_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
sales_with_comparison AS (
    SELECT 
        sales_month,
        monthly_revenue,
        -- 环比：与上月比较
        LAG(monthly_revenue, 1) OVER (ORDER BY sales_month) as prev_month_revenue,
        -- 同比：与去年同月比较
        LAG(monthly_revenue, 12) OVER (ORDER BY sales_month) as same_month_last_year
    FROM monthly_sales
)
SELECT 
    sales_month,
    monthly_revenue,
    prev_month_revenue,
    same_month_last_year,
    
    -- 环比增长率
    CASE 
        WHEN prev_month_revenue IS NOT NULL AND prev_month_revenue > 0 THEN
            ROUND((monthly_revenue - prev_month_revenue) / prev_month_revenue * 100, 2)
        ELSE NULL 
    END as mom_growth_rate,
    
    -- 同比增长率
    CASE 
        WHEN same_month_last_year IS NOT NULL AND same_month_last_year > 0 THEN
            ROUND((monthly_revenue - same_month_last_year) / same_month_last_year * 100, 2)
        ELSE NULL 
    END as yoy_growth_rate
    
FROM sales_with_comparison
ORDER BY sales_month;

🔴 高级面试题

题目7：层次数据查询（员工上下级关系）⭐⭐⭐⭐

题目描述： 有员工表employees(id, name, manager_id)，查找每个员工的完整上级链路。

-- MySQL 8.0+ 递归CTE
WITH RECURSIVE employee_hierarchy AS (
    -- 起始点：所有员工
    SELECT 
        id,
        name,
        manager_id,
        name as hierarchy_path,
        0 as level
    FROM employees
    
    UNION ALL
    
    -- 递归部分：向上查找上级
    SELECT 
        eh.id,
        eh.name,
        e.manager_id,
        CONCAT(e.name, ' -> ', eh.hierarchy_path) as hierarchy_path,
        eh.level + 1
    FROM employee_hierarchy eh
    INNER JOIN employees e ON eh.manager_id = e.id
    WHERE eh.manager_id IS NOT NULL
)
SELECT 
    id,
    name,
    -- 获取完整的上级链路（取最长路径）
    hierarchy_path as full_hierarchy
FROM employee_hierarchy
WHERE (id, level) IN (
    SELECT id, MAX(level)
    FROM employee_hierarchy
    GROUP BY id
)
ORDER BY id;

WITH RECURSIVE subordinate_tree AS (
    -- 起始点：所有顶级管理者
    SELECT 
        id,
        name,
        manager_id,
        0 as level,
        CAST(name AS CHAR(1000)) as hierarchy_path
    FROM employees
    WHERE manager_id IS NULL  -- 从顶级管理者开始
    
    UNION ALL
    
    -- 递归部分：向下查找下属
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        st.level + 1,
        CONCAT(st.hierarchy_path, ' -> ', e.name)
    FROM subordinate_tree st
    INNER JOIN employees e ON st.id = e.manager_id
)
SELECT 
    id,
    name,
    level,
    hierarchy_path
FROM subordinate_tree
ORDER BY level, id;

题目8：复杂的数据透视和反透视 ⭐⭐⭐⭐

题目描述： 有销售数据，需要按产品类别和月份进行透视分析。

-- 销售数据透视（每个产品类别按月份展示销售额）
SELECT 
    product_category,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN total_amount ELSE 0 END) as Jan,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN total_amount ELSE 0 END) as Feb,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN total_amount ELSE 0 END) as Mar,
    SUM(CASE WHEN MONTH(order_date) = 4 THEN total_amount ELSE 0 END) as Apr,
    SUM(CASE WHEN MONTH(order_date) = 5 THEN total_amount ELSE 0 END) as May,
    SUM(CASE WHEN MONTH(order_date) = 6 THEN total_amount ELSE 0 END) as Jun,
    SUM(CASE WHEN MONTH(order_date) = 7 THEN total_amount ELSE 0 END) as Jul,
    SUM(CASE WHEN MONTH(order_date) = 8 THEN total_amount ELSE 0 END) as Aug,
    SUM(CASE WHEN MONTH(order_date) = 9 THEN total_amount ELSE 0 END) as Sep,
    SUM(CASE WHEN MONTH(order_date) = 10 THEN total_amount ELSE 0 END) as Oct,
    SUM(CASE WHEN MONTH(order_date) = 11 THEN total_amount ELSE 0 END) as Nov,
    SUM(CASE WHEN MONTH(order_date) = 12 THEN total_amount ELSE 0 END) as Dec,
    SUM(total_amount) as Total
FROM orders o
INNER JOIN products p ON o.product_id = p.id
WHERE YEAR(order_date) = 2024
GROUP BY product_category
ORDER BY Total DESC;

-- 将横向月份数据转为纵向
WITH monthly_sales AS (
    SELECT 
        product_category,
        SUM(CASE WHEN MONTH(order_date) = 1 THEN total_amount ELSE 0 END) as Jan,
        SUM(CASE WHEN MONTH(order_date) = 2 THEN total_amount ELSE 0 END) as Feb,
        SUM(CASE WHEN MONTH(order_date) = 3 THEN total_amount ELSE 0 END) as Mar
        -- ... 其他月份
    FROM orders o
    INNER JOIN products p ON o.product_id = p.id
    WHERE YEAR(order_date) = 2024
    GROUP BY product_category
)
SELECT product_category, 'Jan' as month, Jan as sales_amount FROM monthly_sales
UNION ALL
SELECT product_category, 'Feb' as month, Feb as sales_amount FROM monthly_sales
UNION ALL
SELECT product_category, 'Mar' as month, Mar as sales_amount FROM monthly_sales
-- ... 其他月份
ORDER BY product_category, 
         FIELD(month, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 
                      'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec');

题目9：复杂业务场景：电商漏斗分析 ⭐⭐⭐⭐⭐

题目描述： 练习统计有商品浏览行为的用户中，各事件的覆盖人数及人数比值；当前示例不约束事件顺序或转化时间窗口，不能直接作为有序转化漏斗。

WITH user_funnel_events AS (
    -- 收集所有用户事件
    SELECT 
        user_id,
        'page_view' as event_type,
        created_at as event_time
    FROM page_views 
    WHERE page_type = 'product'
    
    UNION ALL
    
    SELECT 
        user_id,
        'add_to_cart' as event_type,
        created_at
    FROM cart_additions
    
    UNION ALL
    
    SELECT 
        user_id,
        'checkout_start' as event_type,
        created_at
    FROM checkout_starts
    
    UNION ALL
    
    SELECT 
        user_id,
        'payment_success' as event_type,
        created_at
    FROM orders
    WHERE status = 'completed'
),
user_funnel_summary AS (
    SELECT 
        user_id,
        MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) as has_page_view,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) as has_add_to_cart,
        MAX(CASE WHEN event_type = 'checkout_start' THEN 1 ELSE 0 END) as has_checkout_start,
        MAX(CASE WHEN event_type = 'payment_success' THEN 1 ELSE 0 END) as has_payment_success,
        
        -- 获取各步骤的时间戳
        MIN(CASE WHEN event_type = 'page_view' THEN event_time END) as first_page_view,
        MIN(CASE WHEN event_type = 'add_to_cart' THEN event_time END) as first_add_to_cart,
        MIN(CASE WHEN event_type = 'checkout_start' THEN event_time END) as first_checkout_start,
        MIN(CASE WHEN event_type = 'payment_success' THEN event_time END) as first_payment_success
    FROM user_funnel_events
    GROUP BY user_id
),
funnel_analysis AS (
    SELECT 
        COUNT(DISTINCT user_id) as total_users,
        
        -- 各步骤用户数
        SUM(has_page_view) as page_view_users,
        SUM(has_add_to_cart) as add_to_cart_users,
        SUM(has_checkout_start) as checkout_start_users,
        SUM(has_payment_success) as payment_success_users,
        
        -- 转化率计算
        ROUND(SUM(has_add_to_cart) * 100.0 / NULLIF(SUM(has_page_view), 0), 2) as page_to_cart_rate,
        ROUND(SUM(has_checkout_start) * 100.0 / NULLIF(SUM(has_add_to_cart), 0), 2) as cart_to_checkout_rate,
        ROUND(SUM(has_payment_success) * 100.0 / NULLIF(SUM(has_checkout_start), 0), 2) as checkout_to_payment_rate,
        ROUND(SUM(has_payment_success) * 100.0 / NULLIF(SUM(has_page_view), 0), 2) as overall_conversion_rate
    FROM user_funnel_summary
    WHERE has_page_view = 1  -- 只统计有浏览行为的用户
)
SELECT 
    '商品浏览' as funnel_step,
    page_view_users as user_count,
    100.00 as conversion_rate
FROM funnel_analysis

UNION ALL

SELECT 
    '加入购物车' as funnel_step,
    add_to_cart_users as user_count,
    page_to_cart_rate as conversion_rate
FROM funnel_analysis

UNION ALL

SELECT 
    '开始结账' as funnel_step,
    checkout_start_users as user_count,
    cart_to_checkout_rate as conversion_rate
FROM funnel_analysis

UNION ALL

SELECT 
    '完成支付' as funnel_step,
    payment_success_users as user_count,
    checkout_to_payment_rate as conversion_rate
FROM funnel_analysis;

题目10：性能优化实战题 ⭐⭐⭐⭐⭐

题目描述： 有一个慢查询，需要分析并优化其性能。

-- 问题查询：查找每个用户最近的订单信息
SELECT 
    u.id,
    u.name,
    o.order_date,
    o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.order_date = (
    SELECT MAX(order_date) 
    FROM orders o2 
    WHERE o2.user_id = u.id
)
ORDER BY u.id;

-- 方法一：使用窗口函数
SELECT 
    u.id,
    u.name,
    o.order_date,
    o.total_amount
FROM users u
INNER JOIN (
    SELECT 
        user_id,
        order_date,
        total_amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn
    FROM orders
) o ON u.id = o.user_id AND o.rn = 1
ORDER BY u.id;

-- 方法二：使用FIRST_VALUE窗口函数和DISTINCT改写
SELECT 
    u.id,
    u.name,
    latest_orders.order_date,
    latest_orders.total_amount
FROM users u
INNER JOIN (
    SELECT DISTINCT
        user_id,
        FIRST_VALUE(order_date) OVER (PARTITION BY user_id ORDER BY order_date DESC) as order_date,
        FIRST_VALUE(total_amount) OVER (PARTITION BY user_id ORDER BY order_date DESC) as total_amount
    FROM orders
) latest_orders ON u.id = latest_orders.user_id
ORDER BY u.id;

🎓 面试技巧和注意事项

💡 解题策略

⚠️ 常见陷阱

📚 按公司类型分类

🏢 互联网公司常考

• 用户行为分析
• 增长指标计算
• A/B测试分析
• 推荐系统相关

🏦 金融公司常考

• 风险指标计算
• 合规报告生成
• 异常检测分析
• 资产质量评估

🏭 传统企业常考

• 销售报表分析
• 库存管理查询
• 财务数据统计
• 运营效率分析

📊 模拟面试练习

练习1：第N高薪资 ⭐⭐⭐

编写SQL查询，获取Employee表中第二高的薪资。如果不存在第二高的薪资，查询应该返回null。

-- 方法一：使用LIMIT和OFFSET
SELECT 
    (SELECT DISTINCT salary 
     FROM employees 
     ORDER BY salary DESC 
     LIMIT 1 OFFSET 1) as SecondHighestSalary;

-- 方法二：使用窗口函数
SELECT 
    (SELECT salary
     FROM (
         SELECT salary, 
                DENSE_RANK() OVER (ORDER BY salary DESC) as rk
         FROM employees
     ) t
     WHERE rk = 2
     LIMIT 1) as SecondHighestSalary;

练习2：部门工资前三高的员工 ⭐⭐⭐⭐

查找每个部门工资前三高的员工。

SELECT 
    d.name as Department,
    e.name as Employee,
    e.salary as Salary
FROM (
    SELECT 
        name,
        salary,
        department_id,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rk
    FROM employees
) e
INNER JOIN departments d ON e.department_id = d.id
WHERE e.rk <= 3
ORDER BY d.name, e.salary DESC;

练习3：连续出现N次的数字 ⭐⭐⭐⭐⭐

找出所有至少连续出现三次的数字。

SELECT DISTINCT num as ConsecutiveNums
FROM (
    SELECT 
        num,
        LAG(num, 1) OVER (ORDER BY id) as prev1,
        LAG(num, 2) OVER (ORDER BY id) as prev2
    FROM logs
) t
WHERE num = prev1 AND num = prev2;

🔗 知识连接

学习路径：

• ⬅️ 前置：SQL基础查询, Hive/窗口函数与, 性能优化/性能优化导览
• ➡️ 后续：数据库设计、实际项目应用案例、大数据技术
• 🔄 相关：算法设计、系统设计技术、业务分析框架

面试准备建议：

• 🎯 SQL数据查询语言面试准备 - 快速解题模板
• 🎯 性能优化技术面试准备 - 优化要点清单
• 🎯 业务场景分析技术面试准备 - 常见业务问题

创建时间：2024-06-29
最后更新：2024-06-29
字数统计：约15000字
阅读时长：120-150分钟

#SQL #面试题 #算法 #数据分析 #查询优化 #窗口函数 #业务分析 #高级查询