10道考察SQL能力的笔试题以下是10道考察SQL能力的笔试题，涵盖基础查询、聚合分析、多表关联、子查询、窗口函数等核心

以下是10道考察SQL能力的笔试题，涵盖基础查询、聚合分析、多表关联、子查询、窗口函数等核心知识点，附答案及解析：

1. 基础查询与排序

题目：现有学生表student（结构：idint, namevarchar, ageint, scoreint, class_idint），请查询class_id=2且score>80的学生，按score降序排列，取前3名。

答案：

SELECT id, name, score 
FROM student 
WHERE class_id = 2 AND score > 80 
ORDER BY score DESC 
LIMIT 3;

解析：考察WHERE筛选、ORDER BY排序（DESC降序）、LIMIT限制结果行数。

2. 聚合函数与分组

题目：现有订单表orders（结构：order_idint, user_idint, amountdecimal, order_timedate），请统计2023年每个用户的订单总金额（total_amount），并筛选出总金额超过1000的用户，按总金额降序排列。

答案：

SELECT user_id, SUM(amount) AS total_amount 
FROM orders 
WHERE YEAR(order_time) = 2023 
GROUP BY user_id 
HAVING total_amount > 1000 
ORDER BY total_amount DESC;

解析：考察SUM聚合函数、GROUP BY分组、HAVING筛选分组结果（区别于WHERE，HAVING用于分组后筛选）、YEAR()日期函数。

3. 内连接查询

题目：现有员工表emp（emp_idint, namevarchar, dept_idint）和部门表dept（dept_idint, dept_namevarchar），请查询所有员工的emp_id、name及所属部门名称dept_name（只显示有对应部门的员工）。

答案：

SELECT e.emp_id, e.name, d.dept_name 
FROM emp e 
INNER JOIN dept d 
ON e.dept_id = d.dept_id;

解析：考察INNER JOIN内连接（只保留两表匹配的记录）、表别名（e、d）、连接条件ON。

4. 左连接查询

题目：基于第3题的表，查询所有部门（包括没有员工的部门）的dept_name及该部门的员工人数（emp_count，没有员工则显示0）。

答案：

SELECT d.dept_name, COUNT(e.emp_id) AS emp_count 
FROM dept d 
LEFT JOIN emp e 
ON d.dept_id = e.dept_id 
GROUP BY d.dept_id, d.dept_name;

解析：考察LEFT JOIN左连接（保留左表dept所有记录）、COUNT(e.emp_id)（NULL不计数，实现0的效果）、按部门分组。

5. 子查询（作为条件）

题目：现有产品表product（idint, namevarchar, pricedecimal, categoryvarchar），请查询“电子产品”类别中，价格高于该类别平均价格的产品名称和价格。

答案：

SELECT name, price 
FROM product 
WHERE category = '电子产品' 
AND price > (
    SELECT AVG(price) 
    FROM product 
    WHERE category = '电子产品'
);

解析：考察标量子查询（返回单个值，作为外层查询的条件）、AVG平均值函数。

6. 窗口函数（排名）

题目：现有学生成绩表score（idint, student_idint, subjectvarchar, scoreint），请查询每个科目中，学生成绩的排名（rank，成绩相同排名相同，下一名跳过），显示subject、student_id、score、rank。

答案：

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

解析：考察窗口函数RANK()（排名函数）、PARTITION BY（按科目分组计算排名）、ORDER BY（按成绩降序排序）。

7. 数据更新（带条件）

题目：现有用户表user（idint, namevarchar, balancedecimal, statusvarchar），请将status='inactive'且balance=0的用户状态更新为'deleted'。

答案：

UPDATE user 
SET status = 'deleted' 
WHERE status = 'inactive' AND balance = 0;

解析：考察UPDATE语句的条件更新，注意生产环境中需谨慎使用UPDATE（建议先备份或加限制条件）。

8. 复杂聚合（多条件统计）

题目：现有订单表orders（order_idint, user_idint, amountdecimal, pay_typevarchar, order_datedate），请统计2023年每个月（month）的支付宝支付订单数（alipay_count）和微信支付订单数（wechat_count）。

答案：

SELECT 
  MONTH(order_date) AS month,
  SUM(CASE WHEN pay_type = 'alipay' THEN 1 ELSE 0 END) AS alipay_count,
  SUM(CASE WHEN pay_type = 'wechat' THEN 1 ELSE 0 END) AS wechat_count
FROM orders 
WHERE YEAR(order_date) = 2023 
GROUP BY MONTH(order_date) 
ORDER BY month;

解析：考察CASE条件表达式结合SUM实现多维度统计（行转列思想）、MONTH()日期函数。

9. 子查询与连接结合

题目：现有订单表orders（order_idint, user_idint, order_timedatetime）和用户表user（user_idint, register_timedatetime），请查询“注册后3天内下单”的用户user_id及首次下单时间（first_order_time）。

答案：

SELECT 
  o.user_id, 
  MIN(o.order_time) AS first_order_time 
FROM orders o 
INNER JOIN user u 
ON o.user_id = u.user_id 
WHERE o.order_time <= DATE_ADD(u.register_time, INTERVAL 3 DAY) 
GROUP BY o.user_id;

解析：考察DATE_ADD日期计算函数、INNER JOIN关联两表、MIN求首次时间、子查询的替代方案（用连接+条件实现）。

10. 索引与查询优化（分析题）

题目：现有表goods（idint PRIMARY KEY, namevarchar(100), categoryvarchar(50), pricedecimal, stockint），执行以下查询时效率低下：

SELECT id, name 
FROM goods 
WHERE category = '食品' AND price < 50 
ORDER BY stock DESC;

请分析可能的原因，并给出优化方案。

答案：

低效原因：未在查询条件category、price和排序字段stock上建立合适的索引，导致全表扫描和文件排序。
优化方案：创建复合索引：
```
CREATE INDEX idx_category_price_stock ON goods(category, price, stock);
```
（索引顺序：先筛选字段category、price，再包含排序字段stock，实现索引覆盖查询，避免回表）

解析：考察索引设计原则（最左前缀匹配、筛选字段优先、包含排序/查询字段）、查询优化思路。

总结

以上题目覆盖SQL核心能力：

基础操作（查询、排序、限制结果）；
聚合与分组（GROUP BY、HAVING、条件统计）；
多表关联（内连接、左连接）；
子查询与窗口函数（复杂逻辑实现）；
数据修改与索引优化（实战能力）。

掌握这些题型可应对大部分SQL笔试场景，关键在于理解业务场景与SQL语法的结合，以及性能优化意识。