OrangeDBM中文: 2026

星期三, 四月 29, 2026

SQL窗口函数面试题与解答2

查找每个部门薪资排名前两名的员工

SELECT * FROM ( SELECT 'OrangeDBM' as AUTHOR,department, name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees ) t WHERE rn <= 2;

示例：日环比销售额 SELECT 'OrangeDBM' as AUTHOR,date, sales, LAG(sales) OVER (ORDER BY date) AS prev_day_sales FROM sales;

示例：将当前日期与下一日期进行比较

SELECT 'OrangeDBM' as AUTHOR,date, sales, LEAD(sales) OVER (ORDER BY date) AS next_day_sales FROM sales;

计算每日环比增长率

SELECT 'OrangeDBM' as AUTHOR,date, sales - LAG(sales) OVER (ORDER BY date) AS growth FROM sales;

SQL窗口函数面试题与解答1





示例：按薪资对员工进行排序
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

示例：按部门对员工进行排名
SELECT 'OrangeDBM' as AUTHOR,department, name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

SELECT 'OrangeDBM' as AUTHOR,name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

SELECT 'OrangeDBM' as AUTHOR,name, salary, RANK() OVER (ORDER BY salary DESC) AS rn
FROM employees;

SELECT 'OrangeDBM' as AUTHOR,name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rn
FROM employees;

SQL学习路线地图

-- 创建学生表
CREATETABLE students (
    id INTPRIMARYKEY,
    name VARCHAR(50),
    age INT
);

-- 插入新数据 (Create)
INSERTINTO students (id, name, age) VALUES (1, '张三', 20);


-- 查找年龄大于 18 的学生，按年龄降序排列
SELECTDISTINCT name, age 
FROM students 
WHERE age >18
ORDERBY age DESC
LIMIT5;


-- 计算平均年龄
SELECT AVG(age) FROM students;

-- 合并姓名与固定字符串
SELECT CONCAT('学生姓名: ', name) AS student_info FROM students;


-- 查询学生及其选修的课程名
SELECT students.name, courses.course_name
FROM students
INNERJOIN enrollments ON students.id = enrollments.student_id
INNERJOIN courses ON enrollments.course_id = courses.id;


-- 查找成绩高于平均分的学生 ID
SELECT student_id 
FROM grades 
WHERE score > (SELECT AVG(score) FROM grades);


CREATETABLE orders (
    order_id INTPRIMARYKEY,  -- 主键：唯一标识
    user_id INTNOTNULL,      -- 非空约束
FOREIGNKEY (user_id) REFERENCES users(id) -- 外键：关联用户表
);


-- 为姓名列创建索引以加速查询
CREATE INDEX idx_student_name ON students(name);

-- 创建一个只显示优秀学生的视图
CREATEVIEW elite_students AS
SELECT name, score FROM students WHERE score >90;


START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- 如果没有错误则提交，否则执行 ROLLBACK;
COMMIT;

-- 当删除学生时，自动记录日志
CREATE TRIGGER after_student_delete
AFTER DELETE ON students
FOR EACH ROW
INSERT INTO logs (action, student_id) VALUES ('DELETE', OLD.id);

SQL场景化面试题

SQL 代码：


WITH RankedOrders AS (
    SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id, product
        ORDER BY id ASC
    ) as rn
    FROM orders
)
DELETE FROM orders
WHERE id IN (
    SELECT id FROM RankedOrders
    WHERE rn > 1
);

SQL思维导图

SQL 核心分类

1. DDL（数据定义语言）

用于定义或更改数据库的结构。

ALTER（更改）：

ADD（添加）：列、约束（唯一约束、非空约束、主键、外键、检查约束、默认值）

REMOVE（移除）：列、约束

MODIFY（修改）：数据类型、默认值

VIEW（查看）：数据库、表、视图

DROP（删除）：表、数据库

TRUNCATE（截断）：表

2. DML（数据操纵语言）

用于管理对象内部的数据。

SELECT（查询）：指定列、整张表 (*)

INSERT（插入）：数据至表

UPDATE（更新）：字段

DELETE（删除）：字段

3. DCL 与 TCL（数据/事务控制）

用于权限管理及事务管理。

GRANT（授权）/ REVOKE（撤销权限）

COMMIT（提交）/ ROLLBACK（回滚）/ SAVEPOINT（保存点）

4. 查询子句与逻辑

ALIAS（别名）：AS

GROUP BY（分组）：按列分组、HAVING（分组过滤）

ORDER BY（排序）：升序 (ASC)、降序 (DESC)

JOIN（连接）：内连接 (INNER)、左连接 (LEFT)、右连接 (RIGHT)、全连接 (FULL)

WHERE（条件子句/运算符）：比较运算符（<, >, =, 等）、AND/OR/NOT、BETWEEN、LIKE、IN、ANY、ALL、EXISTS

5. 函数

AGGREGATE FUNCTIONS（聚合函数）：AVG()、SUM()、COUNT()、MIN()、MAX()

WINDOW FUNCTIONS（窗口函数）：

OVER()、ROW_NUMBER()

RANK()、DENSE_RANK()

NTILE()、LAG()、LEAD()

SQL学习路线

SQL示例

SELECT 
    Product_Name AS Name,           -- ALIAS
    SUM(Amount) AS Total_Sales      -- FUNCTIONS (SUM)
FROM Sales
WHERE Region IN ('North', 'East')   -- WHERE & IN
GROUP BY Product_Name               -- GROUP BY
HAVING SUM(Amount) > 1000           -- HAVING
ORDER BY Total_Sales DESC;          -- ORDER BY & DESC

SQL Concepts

1. ALIAS

2. GROUP BY

COLUMN
- HAVING

3. WHERE

LIKE
IN
BETWEEN
ANY
EXIST
AND OR NOT

4. FUNCTIONS

AVG( )
SUM( )
COUNT( )
MIN( )
MAX( )

5. JOINS

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN

6. ORDER BY

ASC
DESC

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`id` int(0) NULL DEFAULT NULL,
`productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`price` int(0) NULL DEFAULT NULL,
`stock_qty` int(0) NULL DEFAULT NULL,
`supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

在每个类别内按价格对产品进行排序


SELECT 'OrangeDBM' as AUTHOR, product_name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

查找拥有超过 3 种产品的供应商


SELECT 'OrangeDBM' as AUTHOR, supplier_id, COUNT(*)
FROM products
GROUP BY supplier_id
HAVING COUNT(*) > 3;

查找价格最高的 3 件产品


SELECT DISTINCT 'OrangeDBM' as AUTHOR, price
FROM products
ORDER BY price DESC
LIMIT 3;

SQL 问与答5

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for products
-- ----------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`id` int(0) NULL DEFAULT NULL,
`productname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`category` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`price` int(0) NULL DEFAULT NULL,
`stock_qty` int(0) NULL DEFAULT NULL,
`supplier_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of products
-- ----------------------------
INSERT INTO `products` VALUES (1, 'Laptop', 'Electronics', 75000, 15, 101);
INSERT INTO `products` VALUES (2, 'Mouse', 'Electronics', 1500, 50, 102);
INSERT INTO `products` VALUES (3, 'Chair', 'Furniture', 8000, 25, 103);
INSERT INTO `products` VALUES (4, 'Keyboard', 'Electronics', 2500, 30, 102);
INSERT INTO `products` VALUES (5, 'Desk', 'Furniture ', 15000, 10, 103);
INSERT INTO `products` VALUES (6, 'Monitor', 'Electronics', 25000, 20, 101);
INSERT INTO `products` VALUES (7, 'Table', 'Furniture', 5000, 40, 104);
INSERT INTO `products` VALUES (8, 'Webcam', 'Electronics', 3000, 35, 102);

SET FOREIGN_KEY_CHECKS = 1;

按供应商统计产品总数


SELECT 'OrangeDBM' as AUTHOR, supplier_id, COUNT(*) AS product_count
FROM products
GROUP BY supplier_id;

查找包含超过 2 个产品的类别


SELECT 'OrangeDBM' as AUTHOR, category, COUNT(*) AS total_products
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

查找整体第二高的价格


SELECT 'OrangeDBM' as AUTHOR, MAX(price)
FROM products
WHERE price < (SELECT MAX(price) FROM products);

查找最昂贵的电子产品


SELECT 'OrangeDBM' as AUTHOR, productname, price
FROM products
WHERE category = 'Electronics'
AND price = (SELECT MAX(price) FROM products WHERE category = 'Electronics');

星期二, 四月 28, 2026

SQL 问与答4

计算每个产品类别的平均价格

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

查找价格高于其所属类别平均价格的产品

SELECT productname, category, price
FROM products p
WHERE price > (SELECT AVG(price)
FROM products
WHERE category = p.category);

找出每个类别中价格最高的产品

SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category;

查找库存数量少于 20 的产品

SELECT 'OrangeDBM' as AUTHOR, productname, stock_qty
FROM products
WHERE stock_qty < 20;

星期一, 四月 27, 2026

SQL 问与答3

SET FOREIGN_KEY_CHECKS = 1;

按薪资对员工进行排序

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

查找重复的薪资

SELECT salary, COUNT() FROM employees GROUP BY salary HAVING COUNT() > 1;

薪资最高两位

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 2;

SQL 问与答2

SET FOREIGN_KEY_CHECKS = 1;

统计各部门的员工人数

SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department;

查找员工人数超过 2 人的部门

SELECT department, COUNT() AS total FROM employees GROUP BY department HAVING COUNT() > 2;

查找第二高的薪资

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

查找没有上级的员工

SELECT name FROM employees WHERE manager_id IS NULL;

SQL 问与答1

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(0) NULL DEFAULT NULL,
`name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`salary` int(0) NULL DEFAULT NULL,
`manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

SET FOREIGN_KEY_CHECKS = 1;

计算各部门的平均薪资

SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department;

查找薪资高于部门平均水平的员工

SELECT name, department, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department );

查找每个部门的最高薪资

SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department;

SQL速查3

SET FOREIGN_KEY_CHECKS = 1;

11.CASE WHEN

SELECT name, CASE WHEN salary > 40000 THEN 'High' ELSE 'Low' END AS category FROM employees;

12.子查询

SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees);

13.RANK()

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;

14.DISTINCT

SELECT DISTINCT department FROM employees;

15.LIKE

SELECT * FROM employees WHERE name LIKE 'A%';

SQL速查2

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for departments
-- ----------------------------
DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (
`DepartmentID` int(0) NOT NULL AUTO_INCREMENT,
`DepartmentName` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
PRIMARY KEY (`DepartmentID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of departments
-- ----------------------------
INSERT INTO `departments` VALUES (1, 'HR');
INSERT INTO `departments` VALUES (2, 'Finance');
INSERT INTO `departments` VALUES (3, 'Engineering');
INSERT INTO `departments` VALUES (4, 'Sales');
INSERT INTO `departments` VALUES (5, 'Marketing');
INSERT INTO `departments` VALUES (6, 'IT');
INSERT INTO `departments` VALUES (7, 'Operations');
INSERT INTO `departments` VALUES (8, 'Customer Service');
INSERT INTO `departments` VALUES (9, 'Research and Department');
INSERT INTO `departments` VALUES (10, 'Legal');
INSERT INTO `departments` VALUES (11, 'Quality Assurance');

SET FOREIGN_KEY_CHECKS = 1;

6.AVG()

SELECT AVG(salary) FROM employees;

7.GROUP BY

SELECT department, AVG(salary) FROM employees GROUP BY department;

8.HAVING

SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 40000;

9.INNER JOIN

SELECT e.name, d.departmentname FROM employees e INNER JOIN departments d ON e.department = d.departmentname;

10.LEFT JOIN

SELECT e.name, d.departmentname FROM employees e LEFT JOIN departments d ON e.department = d.departmentname;

SQL速查1

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

1.SELECT

SELECT name, salary FROM employees;

2.FROM

SELECT * FROM employees;

3.WHERE

SELECT * FROM employees WHERE salary > 30000;

4.ORDER BY

SELECT * FROM employees ORDER BY salary DESC;

5.COUNT()

SELECT COUNT(*) FROM employees;

精选 SQL 问题及解答2

SET NAMES utf8mb4;

SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for sales

-- ----------------------------

DROP TABLE IF EXISTS `sales`;

CREATE TABLE `sales` (
`SaleID` int(0) NOT NULL,
`EmployeeID` int(0) NULL DEFAULT NULL,
`Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`Sales` decimal(10, 2) NULL DEFAULT NULL,
`Date` date NULL DEFAULT NULL,
PRIMARY KEY (`SaleID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 101, 'Electronics', 500.00, '2023-08-01');
INSERT INTO `sales` VALUES (2, 102, 'Electronics', 300.00, '2023-08-03');
INSERT INTO `sales` VALUES (3, 101, 'Furniture', 150.00, '2023-08-02');
INSERT INTO `sales` VALUES (4, 103, 'Electronics', 250.00, '2023-08-04');
INSERT INTO `sales` VALUES (5, 104, 'Furniture', 200.00, '2023-08-02');
-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(0) NULL DEFAULT NULL,
`name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`salary` int(0) NULL DEFAULT NULL,
`manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);
-- ----------------------------
-- Table structure for customers
-- ----------------------------
DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
`customer_id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
PRIMARY KEY (`customer_id`) USING BTREE,
UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES (1, 'John Doe', 'john@example.com', '1234567890');
INSERT INTO `customers` VALUES (2, 'Jane Smith', 'jane@example.com', '0987654321');
INSERT INTO `customers` VALUES (5, 'Blau Hoss', 'Blau@example.com', '11111111');

SET FOREIGN_KEY_CHECKS = 1;

问题描述：按日期计算销售额的累计总计。

解决方案

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

问题描述：找出薪资高于平均水平的员工。

解决方案

SELECT name, salary FROM employees WHERE salary > ( SELECT AVG(salary) FROM employees );

问题描述：找出总薪资最高的部门。

解决方案

SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department ORDER BY total_salary DESC LIMIT 1;

问题描述：找出至少下过一单的客户。

解决方案

SELECT name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE c.customer_id = o.customer_id );

问题描述：删除重复记录，但保留其中一条唯一记录。

解决方案

DELETE FROM employees WHERE id NOT IN ( SELECT MIN(id) FROM employees GROUP BY name );

精选 SQL 问题及解答1

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employees
-- ----------------------------
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(0) NULL DEFAULT NULL,
`name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`department` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`salary` int(0) NULL DEFAULT NULL,
`manager_id` int(0) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employees
-- ----------------------------
INSERT INTO `employees` VALUES (1, 'Aditi', 'HR', 30000, 5);
INSERT INTO `employees` VALUES (2, 'Rahul', 'IT', 50000, 6);
INSERT INTO `employees` VALUES (3, 'Neha', 'IT', 60000, 6);
INSERT INTO `employees` VALUES (4, 'Aman', 'Sales', 40000, 7);
INSERT INTO `employees` VALUES (5, 'Kiran', 'HR', 70000, 0);
INSERT INTO `employees` VALUES (6, 'Mohit', 'IT', 80000, 0);
INSERT INTO `employees` VALUES (7, 'Suresh', 'Sales', 65000, 0);
INSERT INTO `employees` VALUES (8, 'Pooja', 'HR', 30000, 5);

DROP TABLE IF EXISTS `customers`;
CREATE TABLE `customers` (
`customer_id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NOT NULL,
`email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`phone` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
PRIMARY KEY (`customer_id`) USING BTREE,
UNIQUE INDEX `email`(`email`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of customers
-- ----------------------------
INSERT INTO `customers` VALUES (1, 'John Doe', 'john@example.com', '1234567890');
INSERT INTO `customers` VALUES (2, 'Jane Smith', 'jane@example.com', '0987654321');
INSERT INTO `customers` VALUES (5, 'Blau Hoss', 'Blau@example.com', '11111111');

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(0) NULL DEFAULT NULL,
`customer_id` int(0) NULL DEFAULT NULL,
`product` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin NULL DEFAULT NULL,
`order_date` datetime(0) NULL DEFAULT NULL
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES (1, 101, 'Laptop', '2026-01-15 00:00:00');
INSERT INTO `orders` VALUES (3, 102, 'Phone', '2026-01-16 00:00:00');
INSERT INTO `orders` VALUES (4, 101, 'Tablet', '2026-01-17 00:00:00');

SET FOREIGN_KEY_CHECKS = 1;

问题描述：从 employees 表中查找第二高的薪资。

解决方案

SELECT MAX(salary) FROM employees WHERE salary < ( SELECT MAX(salary) FROM employees );

问题描述：编写一条查询语句，查找第 3 高的薪水。

解决方案

SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) r FROM employees ) t WHERE r = 3;

问题描述：找出 employees 表中所有重复的姓名。

解决方案

SELECT name, COUNT() FROM employees GROUP BY name HAVING COUNT() > 1;

问题描述：找出未下过任何订单的客户。

解决方案

SELECT c.name FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.customer_id IS NULL;

问题描述：找出每个部门中薪资最高的3名员工。

解决方案

SELECT * FROM ( SELECT name, department, salary, ROW_NUMBER() OVER( PARTITION BY department ORDER BY salary DESC ) r FROM employees ) t WHERE r <= 3;

SQL查询之窗口函数真实数据分析案例

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------

-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
`SaleID` int(0) NOT NULL,
`EmployeeID` int(0) NULL DEFAULT NULL,
`Department` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
`Sales` decimal(10, 2) NULL DEFAULT NULL,
`Date` date NULL DEFAULT NULL,
PRIMARY KEY (`SaleID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------

-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, 101, 'Electronics', 500.00, '2023-08-01');
INSERT INTO `sales` VALUES (2, 102, 'Electronics', 300.00, '2023-08-03');
INSERT INTO `sales` VALUES (3, 101, 'Furniture', 150.00, '2023-08-02');
INSERT INTO `sales` VALUES (4, 103, 'Electronics', 250.00, '2023-08-04');
INSERT INTO `sales` VALUES (5, 104, 'Furniture', 200.00, '2023-08-02');

薪资最高的前三名

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

销售额累计总计

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

按类别对产品进行排名

SELECT productname, category, price, RANK() OVER(PARTITION BY category ORDER BY price DESC) AS rn FROM products;

订阅：博文 (Atom)

OrangeDBM中文

星期三, 四月 29, 2026

SQL窗口函数面试题与解答2

SQL窗口函数面试题与解答1

SQL学习路线地图

SQL场景化面试题

SQL学习应避免的5大误区

SQL思维导图

SQL学习路线

SQL 问与答6

SQL 问与答5

星期二, 四月 28, 2026

SQL 问与答4

星期一, 四月 27, 2026

SQL 问与答3

SQL 问与答2

SQL 问与答1

SQL速查3

SQL速查2

SQL速查1

精选 SQL 问题及解答2

精选 SQL 问题及解答1

SQL查询之窗口函数真实数据分析案例

SQL窗口函数面试题与解答2

举报滥用情况

搜索此博客