mysql - 相关子查询的外部查询的多个结果
问题描述
我想知道是否可以让内部查询返回多个结果,然后由外部查询查询?
这是一个可重现的示例:
在此示例中,我想知道每个部门中薪水最高的员工的姓名和薪水。
CREATE TABLE Worker (
WORKER_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME CHAR(25),
LAST_NAME CHAR(25),
SALARY INT(15),
JOINING_DATE DATETIME,
DEPARTMENT CHAR(25)
);
INSERT INTO Worker
(WORKER_ID, FIRST_NAME, LAST_NAME, SALARY, JOINING_DATE, DEPARTMENT) VALUES
(001, 'Monika', 'Arora', 100000, '14-02-20 09.00.00', 'HR'),
(002, 'Niharika', 'Verma', 80000, '14-06-11 09.00.00', 'Admin'),
(003, 'Vishal', 'Singhal', 300000, '14-02-20 09.00.00', 'HR'),
(004, 'Amitabh', 'Singh', 500000, '14-02-20 09.00.00', 'Admin'),
(005, 'Vivek', 'Bhati', 500000, '14-06-11 09.00.00', 'Admin'),
(006, 'Vipul', 'Diwan', 200000, '14-06-11 09.00.00', 'Account'),
(007, 'Satish', 'Kumar', 75000, '14-01-20 09.00.00', 'Account'),
(008, 'Geetika', 'Chauhan', 90000, '14-04-11 09.00.00', 'Admin');
找到每个部门的最高薪水很容易:
SELECT MAX(SALARY), DEPARTMENT
FROM Worker
GROUP BY DEPARTMENT;
但是,如何将获得这些薪水的员工的姓名也添加到结果中?
非常感谢!
解决方案
您可以将查询与运算符一起使用IN
:
SELECT * FROM Worker
WHERE (DEPARTMENT, SALARY) IN (
SELECT DEPARTMENT, MAX(SALARY)
FROM Worker
GROUP BY DEPARTMENT
);
或者如果您正在使用带有RANK()
窗口功能的 MySql 8.0+:
SELECT w.*
FROM (
SELECT *, RANK() OVER (PARTITION BY DEPARTMENT ORDER BY SALARY DESC) rnk
FROM Worker
) w
WHERE w.rnk = 1
请参阅演示。
结果:
> WORKER_ID | FIRST_NAME | LAST_NAME | SALARY | JOINING_DATE | DEPARTMENT
> --------: | :--------- | :-------- | -----: | :------------------ | :---------
> 3 | Vishal | Singhal | 300000 | 2014-02-20 09:00:00 | HR
> 4 | Amitabh | Singh | 500000 | 2014-02-20 09:00:00 | Admin
> 5 | Vivek | Bhati | 500000 | 2014-06-11 09:00:00 | Admin
> 6 | Vipul | Diwan | 200000 | 2014-06-11 09:00:00 | Account
推荐阅读
- python - How do I create a command using discord.py?
- python - ModuleNotFoundError:没有名为“pegasus”的模块
- java - 不兼容的类型:列表
> 无法转换为列表 - >
- ios - How to get/post/update/delete data from a mongoDB database with an iOS app?
- python - Value not right while doing cumprod() to get cumulative returns in pandas
- dart - 空安全飞镖中的可选参数是否可以自动为空?如果不是,是否有一种简单的方法可以使我的代码为空安全?
- python - CondaHTTPError: HTTP 000 CONNECTION FAILED for url
- xml - 获取 XML 节点的属性值时出错
- c++ - How to solve Undefined symbols for architecture x86_64 error when using C++ API `llvm::InitializeAllAsmParsers()` in LLVM?
- python - How to fill 2d numpy array with some 2d numpy array