收入超过经理的员工
1、题目:
Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
+----------+
| Employee |
+----------+
| Joe |
+----------+
2、解题步骤:
(1) 创建表:
CREATE TABLE `employee` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Id',
`Salary` decimal(18, 2) DEFAULT NULL COMMENT '薪水',
`Name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
`ManagerId` int(11) DEFAULT NULL COMMENT '经理Id',
PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工表' ROW_FORMAT = Dynamic;
(2) 插入数据:
INSERT INTO `employee` VALUES (1, 70000.00, 'Joe', 3);
INSERT INTO `employee` VALUES (2, 80000.00, 'Henry', 4);
INSERT INTO `employee` VALUES (3, 60000.00, 'Sam', NULL);
INSERT INTO `employee` VALUES (4, 90000.00, 'Max', NULL);
INSERT INTO `employee` VALUES (5, 100000.00, '麻花', 4);
INSERT INTO `employee` VALUES (6, 110000.00, '米线', 3);
(3) 查询SQL:
a、方案一:
SELECT a.Name, a.Salary, b.Name AS ManagerName, b.Salary AS ManagerSalary
FROM employee a, employee b
WHERE a.ManagerId = b.Id
AND a.Salary > b.Salary;
b、方案二:
SELECT a.Name, a.Salary, b.Name AS ManagerName, b.Salary AS ManagerSalary
FROM employee a
JOIN employee b
ON a.ManagerId = b.Id
WHERE a.Salary > b.Salary;
(4) 运行结果: