SQL高频面试题

本篇汇总SQL开发和数据分析岗位常见面试题，附详细解答。

一、基础查询

1. 查询每个部门最高薪资的员工

-- 方法1：窗口函数（推荐）
SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

-- 方法2：子查询
SELECT e.*
FROM employees e
JOIN (
    SELECT dept_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY dept_id
) m ON e.dept_id = m.dept_id AND e.salary = m.max_salary;

2. 查询各部门薪资前三名

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

二、聚合统计

3. 查询各部门薪资总和

SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id;

4. 查询各部门薪资总和，按总和排序

SELECT dept_id, SUM(salary) AS total_salary
FROM employees
GROUP BY dept_id
ORDER BY total_salary DESC;

5. 查询工资高于各部门平均工资的员工

SELECT e.*
FROM employees e
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

三、连接查询

6. 内连接 vs 左连接 vs 右连接

-- 内连接：只返回匹配的行
SELECT *
FROM orders o
INNER JOIN customers c ON o.user_id = c.user_id;

-- 左连接：返回左表所有行，匹配不到为NULL
SELECT *
FROM orders o
LEFT JOIN customers c ON o.user_id = c.user_id;

-- 右连接：返回右表所有行
SELECT *
FROM orders o
RIGHT JOIN customers c ON o.user_id = c.user_id;

7. 查询没有订单的客户

SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.user_id = o.user_id
WHERE o.order_id IS NULL;

-- 或使用NOT IN / NOT EXISTS
SELECT * FROM customers
WHERE user_id NOT IN (SELECT user_id FROM orders);

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = c.user_id);

四、日期处理

8. 查询本月数据

-- MySQL
SELECT * FROM orders
WHERE DATE_FORMAT(order_date, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m');

-- Oracle
SELECT * FROM orders
WHERE TO_CHAR(order_date, 'YYYY-MM') = TO_CHAR(SYSDATE, 'YYYY-MM');

9. 查询近7天数据

SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

10. 计算两个日期相差天数

-- MySQL
SELECT DATEDIFF('2024-01-20', '2024-01-14');  -- 6

-- Oracle
SELECT TO_DATE('2024-01-20') - TO_DATE('2024-01-14') FROM dual;

五、字符串处理

11. 字符串拼接

-- MySQL
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM users;

-- Oracle
SELECT first_name || ' ' || last_name AS full_name FROM dual;

12. 字符串截取

-- MySQL
SELECT SUBSTR('Hello World', 1, 5);  -- Hello
SELECT SUBSTR('Hello World', 7);      -- World

13. 模糊查询

SELECT * FROM users WHERE name LIKE '%张%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

六、窗口函数

14. 计算累计求和

SELECT
    *,
    SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;

15. 计算环比增长率

SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
          / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2) AS mom_pct
FROM monthly_revenue;

16. 分组排名

SELECT
    *,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank
FROM employees;

七、子查询

17. 查询薪资最高的前3名员工

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 3;

18. 查询工资大于平均工资的员工

SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

19. 查询有多个下属的员工

SELECT m.*
FROM employees m
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.manager_id = m.emp_id
    HAVING COUNT(*) > 1
);

八、UNION vs UNION ALL

-- UNION：去重，排序
SELECT * FROM table1
UNION
SELECT * FROM table2;

-- UNION ALL：不去重，速度快
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

九、数据类型转换

20. 字符串转数字

-- MySQL
SELECT CAST('123' AS UNSIGNED);

-- Oracle
SELECT TO_NUMBER('123') FROM dual;

21. 日期转字符串

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

-- Oracle
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

十、NULL处理

22. NULL值替换

SELECT COALESCE(phone, 'N/A') FROM users;
SELECT IFNULL(phone, 'N/A') FROM users;  -- MySQL
SELECT NVL(phone, 'N/A') FROM users;      -- Oracle

23. 避免除零

SELECT amount / NULLIF(quantity, 0) FROM products;

十一、复杂查询

24. 查询各部门薪资排名第二的员工

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 2;

25. 查询各部门薪资总和，占总和比例

SELECT
    dept_id,
    SUM(salary) AS dept_total,
    ROUND(SUM(salary) * 100.0 / SUM(SUM(salary)) OVER (), 2) AS pct
FROM employees
GROUP BY dept_id;

26. 查询每个月的累计销量

SELECT
    month,
    sales,
    SUM(sales) OVER (ORDER BY month) AS running_total
FROM monthly_sales;

十二、优化相关

27. 什么情况索引失效

-- 1. 使用函数
SELECT * FROM t WHERE YEAR(date_col) = 2024;  -- 失效

-- 2. 使用LIKE通配符在开头
SELECT * FROM t WHERE name LIKE '%abc';  -- 失效

-- 3. 数据类型不匹配
SELECT * FROM t WHERE str_col = 123;  -- str_col是字符串

28. 如何优化慢查询

-- 1. 添加合适索引
CREATE INDEX idx_col ON table(col);

-- 2. 避免SELECT *
SELECT col1, col2 FROM t;

-- 3. 使用EXPLAIN分析
EXPLAIN SELECT * FROM t WHERE col = 'value';

-- 4. 避免子查询，改为JOIN
-- 5. 分区表查询

十三、经典题

29. 学生成绩排名

SELECT
    student_id,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

30. 连续登录天数

WITH login_days AS (
    SELECT
        user_id,
        login_date,
        DATEDIFF(login_date, LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date)) AS diff
    FROM user_logins
)
SELECT
    user_id,
    COUNT(*) AS consecutive_days
FROM login_days
WHERE diff = 1 OR diff IS NULL
GROUP BY user_id, DATE_SUB(login_date, INTERVAL diff DAY);

31. 部门薪资前三名及占比

SELECT
    dept_id,
    emp_id,
    salary,
    ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id), 2) AS pct
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn <= 3;

十四、数据分析题

32. 漏斗分析

SELECT
    '浏览' AS stage,
    COUNT(DISTINCT user_id) AS users
FROM events WHERE event = 'view'
UNION ALL
SELECT
    '加购',
    COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END)
FROM events
UNION ALL
SELECT
    '下单',
    COUNT(DISTINCT CASE WHEN event = 'checkout' THEN user_id END)
FROM events;

33. RFM分析

WITH rfm AS (
    SELECT
        user_id,
        DATEDIFF(CURDATE(), MAX(order_date)) AS recency,
        COUNT(*) AS frequency,
        SUM(amount) AS monetary
    FROM orders
    GROUP BY user_id
)
SELECT
    user_id,
    recency,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency) AS f_score,
    NTILE(5) OVER (ORDER BY monetary) AS m_score
FROM rfm;

34. 留存分析

WITH install_cohort AS (
    SELECT user_id, MIN(DATE(install_time)) AS install_date
    FROM users
    GROUP BY user_id
),
retention AS (
    SELECT
        i.user_id,
        i.install_date,
        DATEDIFF(DATE(a.action_time), i.install_date) AS days
    FROM install_cohort i
    JOIN user_actions a ON i.user_id = a.user_id
)
SELECT
    days,
    COUNT(DISTINCT user_id) AS users,
    ROUND(COUNT(DISTINCT user_id) * 100.0 / (SELECT COUNT(*) FROM install_cohort), 2) AS retention_pct
FROM retention
GROUP BY days
ORDER BY days;