该表必须按部门分组,并且必须返回部门的最大数量
表A:
ID | 姓名 | 部 |
---|---|---|
1个 | 约翰·亚伯拉罕 | 人力资源 |
2 | 迈克尔·克拉克 | 人力资源 |
3 | 罗伊·托马斯(Roy Thomas) | 经理 |
4 | 汤姆·何塞 | 人力资源 |
4 | 杰里·平托 | 经理 |
表B:
中 | 数量 |
---|---|
1个 | 5000 |
2 | 5000 |
3 | 2500 |
4 | 1000 |
4 | 1500 |
预期答案
ID | 姓名 | 部 | 数量 |
---|---|---|---|
1个 | 约翰·亚伯拉罕 | 人力资源 | 5000 |
2 | 迈克尔·克拉克 | 人力资源 | 5000 |
3 | 罗伊·托马斯(Roy Thomas) | 经理 | 2500 |
您可以尝试如下操作:
select main.*
FROM
-- get all information from t1 and amount from t2
(
select t1.*, t2.amount from t1 inner join t2 on t1.id = m_id
) main
INNER JOIN
-- get max amount by department
(
select department, max(amount) max_amount from t1 inner join t2 on t1.id = m_id
group by department
) summary
-- match with main by department and the max amount
on main.department = summary.department
and main.amount = summary.max_amount;
结果
+------+----------------+------------+--------+
| id | name | department | amount |
+------+----------------+------------+--------+
| 1 | John Abraham | HR | 5000 |
| 2 | Michael Clarke | HR | 5000 |
| 3 | Roy Thomas | Manager | 2500 |
+------+----------------+------------+--------+
示例在这里:https : //dbfiddle.uk/?rdbms=mysql_5.7&fiddle=a4cdd94b415df204b0fd967263ba9dc8
解释
由于您要按部门获取最大金额,因此我们为此创建了一个子查询。我们给它起了一个别名summary
。这给了我们这个:
select department, max(amount)
from t1 inner join t2 on t1.id = m_id group by department;
+------------+-------------+
| department | max(amount) |
+------------+-------------+
| HR | 5000 |
| Manager | 2500 |
+------------+-------------+
我们通过合并t1和t2表来合并您要报告的数据,并为其命名main
。这给了我们这个:
select t1.*, t2.amount from t1 inner join t2 on t1.id = m_id;
+------+----------------+------------+--------+
| id | name | department | amount |
+------+----------------+------------+--------+
| 1 | John Abraham | HR | 5000 | <-- want this
| 2 | Michael Clarke | HR | 5000 | <-- want this
| 3 | Roy Thomas | Manager | 2500 | <-- want this
| 4 | Jerry Pinto | Manager | 1000 |
| 4 | Tom Jose | HR | 1000 |
+------+----------------+------------+--------+
然后,我们根据部门和两个子查询中的金额确保两个子查询都已加入。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句