面试高频SQL语句总结

一、窗口函数与排名类问题

1.1 查询每个班级最高分成绩

问题描述：查询每个班最高分成绩

方案一：使用窗口函数（推荐）

-- MySQL 8.0+, PostgreSQL, SQL Server
SELECT class_id, student_name, score
FROM (
    SELECT class_id, student_name, score,
           ROW_NUMBER() OVER (PARTITION BY class_id ORDER BY score DESC) as rn
    FROM students
) t
WHERE rn = 1;

方案二：使用子查询

SELECT s1.class_id, s1.student_name, s1.score
FROM students s1
WHERE s1.score = (
    SELECT MAX(s2.score)
    FROM students s2
    WHERE s2.class_id = s1.class_id
);

方案三：使用JOIN

SELECT s.class_id, s.student_name, s.score
FROM students s
INNER JOIN (
    SELECT class_id, MAX(score) as max_score
    FROM students
    GROUP BY class_id
) m ON s.class_id = m.class_id AND s.score = m.max_score;

1.2 扩展场景：查询每个班级前3名成绩

SELECT class_id, student_name, score
FROM (
    SELECT class_id, student_name, score,
           DENSE_RANK() OVER (PARTITION BY class_id ORDER BY score DESC) as rank_num
    FROM students
) t
WHERE rank_num <= 3;

1.3 查询连续登录N天的用户

-- 查询连续登录3天以上的用户
WITH login_days AS (
    SELECT user_id, login_date,
           DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) as group_key
    FROM user_logins
    GROUP BY user_id, login_date
)
SELECT user_id, COUNT(*) as consecutive_days
FROM login_days
GROUP BY user_id, group_key
HAVING COUNT(*) >= 3;

二、时间范围查询类问题

2.1 查询每个车辆最近一个小时的位置信息

问题描述：位置表包含每个车辆作业时的轨迹信息，查看每个车辆最近一个小时的位置信息

方案一：使用时间范围过滤

SELECT vehicle_id, position_x, position_y, record_time
FROM vehicle_positions
WHERE record_time >= NOW() - INTERVAL 1 HOUR
ORDER BY vehicle_id, record_time DESC;

方案二：查询每个车辆最新的位置（不限时间）

SELECT vehicle_id, position_x, position_y, record_time
FROM (
    SELECT vehicle_id, position_x, position_y, record_time,
           ROW_NUMBER() OVER (PARTITION BY vehicle_id ORDER BY record_time DESC) as rn
    FROM vehicle_positions
) t
WHERE rn = 1;

方案三：每个车辆最近一小时内的最新位置

SELECT vehicle_id, position_x, position_y, record_time
FROM (
    SELECT vehicle_id, position_x, position_y, record_time,
           ROW_NUMBER() OVER (PARTITION BY vehicle_id ORDER BY record_time DESC) as rn
    FROM vehicle_positions
    WHERE record_time >= NOW() - INTERVAL 1 HOUR
) t
WHERE rn = 1;

2.2 扩展场景：查询过去7天每天的活跃用户数

SELECT DATE(login_time) as login_date, COUNT(DISTINCT user_id) as active_users
FROM user_logins
WHERE login_time >= CURDATE() - INTERVAL 7 DAY
GROUP BY DATE(login_time)
ORDER BY login_date;

三、去重与数据清理类问题

3.1 删除重复数据，保留最老的记录

问题描述：表格存在冗余数据，相同的地址记录出现多条，删除重复数据，保留最老的

方案一：使用窗口函数（MySQL 8.0+）

-- 先查看要删除的记录
DELETE FROM addresses
WHERE id NOT IN (
    SELECT min_id FROM (
        SELECT MIN(id) as min_id
        FROM addresses
        GROUP BY address_detail
    ) t
);

方案二：使用自连接

DELETE a1 FROM addresses a1
INNER JOIN addresses a2
WHERE a1.id > a2.id AND a1.address_detail = a2.address_detail;

方案三：使用ROW_NUMBER()（保留最早的）

DELETE FROM addresses
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY address_detail ORDER BY create_time ASC) as rn
        FROM addresses
    ) t
    WHERE rn > 1
);

3.2 扩展场景：删除重复数据，保留最新的记录

DELETE FROM addresses
WHERE id IN (
    SELECT id FROM (
        SELECT id,
               ROW_NUMBER() OVER (PARTITION BY address_detail ORDER BY create_time DESC) as rn
        FROM addresses
    ) t
    WHERE rn > 1
);

3.3 查找重复记录（不删除）

-- 查找所有重复的地址记录
SELECT address_detail, COUNT(*) as duplicate_count
FROM addresses
GROUP BY address_detail
HAVING COUNT(*) > 1;

-- 查看具体的重复记录
SELECT * FROM addresses
WHERE address_detail IN (
    SELECT address_detail
    FROM addresses
    GROUP BY address_detail
    HAVING COUNT(*) > 1
)
ORDER BY address_detail, create_time;

四、聚合与分组统计类问题

4.1 汇总票号明细和货物信息

问题描述：一条船上一个票号有多条明细，总结每个票号的明细，以及货物中相同的信息进行汇总

基础汇总

SELECT 
    ticket_no,
    ship_id,
    COUNT(*) as detail_count,
    SUM(quantity) as total_quantity,
    SUM(amount) as total_amount,
    GROUP_CONCAT(DISTINCT cargo_type) as cargo_types
FROM ticket_details
GROUP BY ticket_no, ship_id
ORDER BY ticket_no;

复杂汇总（按货物类型分组）

SELECT 
    ticket_no,
    cargo_type,
    COUNT(*) as item_count,
    SUM(quantity) as total_quantity,
    AVG(unit_price) as avg_price,
    MAX(expiry_date) as latest_expiry
FROM ticket_details
GROUP BY ticket_no, cargo_type
ORDER BY ticket_no, cargo_type;

使用ROLLUP进行多级汇总

-- 票号 + 货物类型的多级汇总
SELECT 
    COALESCE(ticket_no, 'TOTAL') as ticket_no,
    COALESCE(cargo_type, 'SUB_TOTAL') as cargo_type,
    SUM(quantity) as total_quantity,
    SUM(amount) as total_amount
FROM ticket_details
GROUP BY ticket_no, cargo_type WITH ROLLUP
ORDER BY ticket_no, cargo_type;

4.2 扩展场景：计算每个用户的订单统计

SELECT 
    user_id,
    COUNT(*) as order_count,
    SUM(total_amount) as total_spent,
    AVG(total_amount) as avg_order_value,
    MAX(order_date) as last_order_date
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 2  -- 至少有2个订单的用户
ORDER BY total_spent DESC;

五、表关联更新类问题

5.1 使用表A的数据更新表B

问题描述：表A包含一些数据范围，需要更新相应表B的数据，表A和表B通过字段关联

MySQL语法

UPDATE table_b b
INNER JOIN table_a a ON b.common_field = a.common_field
SET b.target_column = a.source_column,
    b.update_time = NOW()
WHERE a.status = 'active';

PostgreSQL/SQL Server语法

UPDATE table_b
SET target_column = a.source_column,
    update_time = NOW()
FROM table_a a
WHERE table_b.common_field = a.common_field
  AND a.status = 'active';

复杂条件更新

UPDATE table_b b
INNER JOIN table_a a ON b.category_id = a.category_id
SET b.price = CASE
    WHEN a.discount_rate > 0.5 THEN a.base_price * 0.8
    WHEN a.discount_rate > 0.3 THEN a.base_price * 0.9
    ELSE a.base_price
END,
b.discount_applied = TRUE
WHERE a.effective_date <= CURDATE()
  AND a.expiry_date >= CURDATE();

5.2 批量更新多个字段

UPDATE products p
INNER JOIN price_updates pu ON p.product_id = pu.product_id
SET p.current_price = pu.new_price,
    p.last_updated = pu.update_time,
    p.price_version = p.price_version + 1
WHERE pu.update_status = 'approved';

六、其他高频面试SQL场景

6.1 行转列（Pivot）

问题：将学生成绩按科目转为列显示

-- MySQL行转列
SELECT 
    student_name,
    MAX(CASE WHEN subject = '数学' THEN score END) as math_score,
    MAX(CASE WHEN subject = '语文' THEN score END) as chinese_score,
    MAX(CASE WHEN subject = '英语' THEN score END) as english_score
FROM student_scores
GROUP BY student_name;

6.2 列转行（Unpivot）

问题：将宽表转换为长表

-- MySQL列转行
SELECT student_name, 'math' as subject, math_score as score
FROM student_grades
UNION ALL
SELECT student_name, 'chinese' as subject, chinese_score as score
FROM student_grades
UNION ALL
SELECT student_name, 'english' as subject, english_score as score
FROM student_grades
ORDER BY student_name, subject;

6.3 递归查询（树形结构）

问题：查询组织架构中的所有下级

-- MySQL 8.0+ 递归CTE
WITH RECURSIVE org_tree AS (
    -- 锚点：起始节点
    SELECT id, name, parent_id, 0 as level
    FROM departments
    WHERE id = 1  -- 从ID为1的部门开始
    
    UNION ALL
    
    -- 递归：查找下级
    SELECT d.id, d.name, d.parent_id, ot.level + 1
    FROM departments d
    INNER JOIN org_tree ot ON d.parent_id = ot.id
)
SELECT * FROM org_tree
ORDER BY level, name;

6.4 间隔连续性分析

问题：找出用户连续签到的最长天数

WITH consecutive_days AS (
    SELECT 
        user_id,
        sign_date,
        DATE_SUB(sign_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY sign_date) DAY) as group_key
    FROM user_signs
)
SELECT 
    user_id,
    COUNT(*) as consecutive_days,
    MIN(sign_date) as start_date,
    MAX(sign_date) as end_date
FROM consecutive_days
GROUP BY user_id, group_key
ORDER BY consecutive_days DESC
LIMIT 1;

6.5 百分位数计算

问题：计算订单金额的90%分位数

-- MySQL 8.0+
SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY amount) as p90_amount
FROM orders;

-- 兼容性方案
SELECT amount as p90_amount
FROM (
    SELECT amount, 
           ROW_NUMBER() OVER (ORDER BY amount) as rn,
           COUNT(*) OVER () as total_count
    FROM orders
) t
WHERE rn = CEIL(total_count * 0.9);

6.6 累计计算

问题：计算每日累计销售额

SELECT 
    order_date,
    daily_sales,
    SUM(daily_sales) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) as cumulative_sales
FROM (
    SELECT 
        DATE(order_time) as order_date,
        SUM(amount) as daily_sales
    FROM orders
    GROUP BY DATE(order_time)
) daily_stats
ORDER BY order_date;

七、性能优化技巧

7.1 索引优化

• 复合索引顺序：高选择性字段放前面
• 覆盖索引：包含查询所需的所有字段
• 避免函数索引：WHERE YEAR(date_column) = 2023 不会使用索引

7.2 查询优化

• **避免SELECT ***：只选择需要的字段
• 合理使用JOIN：确保关联字段有索引
• 分页优化：深度分页使用游标分页
• EXISTS vs IN：大数据量用EXISTS，小数据量用IN

7.3 执行计划分析

-- MySQL查看执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 重点关注：
-- type: ALL(全表扫描) vs ref(索引查找)
-- key: 使用的索引
-- rows: 扫描行数
-- Extra: 额外信息（Using filesort, Using temporary等）

八、常见陷阱与注意事项

8.1 NULL值处理

-- 错误：NULL = NULL 返回NULL，不是TRUE
SELECT * FROM users WHERE name = NULL;  -- 不会返回任何结果

-- 正确：使用IS NULL
SELECT * FROM users WHERE name IS NULL;

-- 使用COALESCE处理NULL
SELECT COALESCE(name, 'Unknown') as display_name FROM users;

8.2 字符串比较

-- 注意大小写敏感性
-- MySQL默认不区分大小写（取决于collation）
SELECT * FROM users WHERE username = 'Admin';  -- 可能匹配'admin'

-- 强制区分大小写
SELECT * FROM users WHERE BINARY username = 'Admin';

8.3 浮点数精度

-- 避免使用FLOAT/DOUBLE存储金额
-- 使用DECIMAL(10,2)存储精确的金额数据
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    amount DECIMAL(10,2) NOT NULL  -- 精确到分
);

8.4 事务隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

九、面试准备建议

9.1 掌握核心概念

• JOIN类型：INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
• 聚合函数：COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT
• 窗口函数：ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG
• 子查询类型：相关子查询、非相关子查询、EXISTS子查询

9.2 练习策略

1. 从简单到复杂：先掌握基础查询，再练习复杂场景
2. 多解法对比：同一问题尝试多种解决方案
3. 性能考虑：不仅要正确，还要考虑性能
4. 边界情况：考虑NULL值、空结果、重复数据等情况

9.3 常见面试题型

• 排名问题：Top N、分组排名、连续排名
• 时间序列：最近N天、连续时间段、时间间隔
• 数据清洗：去重、缺失值处理、数据标准化
• 聚合统计：分组汇总、交叉表、累计计算
• 关联操作：多表关联、自关联、更新关联

十、总结

面试中的SQL题目主要考察以下几个方面：

1. 基础语法掌握：SELECT、WHERE、GROUP BY、HAVING、ORDER BY
2. 高级功能运用：窗口函数、CTE、递归查询
3. 问题分析能力：将业务需求转化为SQL逻辑
4. 性能意识：考虑查询效率和索引使用
5. 边界情况处理：NULL值、重复数据、空结果集

关键技巧：

• 熟练掌握窗口函数，这是解决大多数复杂问题的关键
• 理解不同JOIN的使用场景
• 学会使用CTE（公用表表达式）让复杂查询更清晰
• 注意数据库方言差异（MySQL、PostgreSQL、Oracle等）

记住：面试官不仅看答案是否正确，更看重你的思考过程和解决问题的方法论。在回答时，可以先分析问题，再给出解决方案，最后讨论可能的优化方向。