如何检索获得最大奖金的员工?

Question

我必须显示获得最大奖金的员工详细信息(一个包含员工详细信息，另一个包含奖金详细信息)。在这里，我创建了一个"绩效奖金"专栏来总结各种奖金。如何从该列检索雇员?

select e.Employee_id,
e.First_name,
e.Department,
e.Salary,
coalesce((select sum(b.Bonus_Amount) 
as Bonus-- Let's sum up all Employee's the bonuses
from Employee_Bonus_Table b
where b.Employee_ref_id = e.Employee_Id), 0) [Performance_bonus]
from Employee_Table e

Answer 1

如果我正确理解了任务，那么奖励就可以重复，对吗?然后我们必须首先把所有员工的奖金加起来，然后从最大到最小排序，从列表中得到第一个

--- for examples:
with Employee_Bonus_Table as(
select Bonus_Amount = 1 ,Employee_ref_id = 1
union select Bonus_Amount = 1000 ,Employee_ref_id = 2
union select Bonus_Amount = 2000 ,Employee_ref_id = 2

),Employee_Table as (
select Employee_id=1
,First_name='First_name'
,Department= 'Department'
,Salary = 1000
UNION select Employee_id=2
,First_name='First_name2'
,Department= 'Department2'
,Salary = 2000
)
--reslut query:
select top 1
e.Employee_id,
e.First_name,
e.Department,
e.Salary,
b.sumBonus_Amount
from Employee_Table e
join (select sumBonus_Amount = sum(Bonus_Amount), Employee_ref_id 
from  Employee_Bonus_Table
group by Employee_ref_id
) b on b.Employee_ref_id = e.Employee_Id
order by b.sumBonus_Amount desc