精选 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;