我必須顯示獲得最高獎金的員工詳細資訊（一個包含員工詳細資訊，另一個表包含獎金詳細資訊）。在這里，我創建了一個“績效獎金”列來總結多項獎金。如何從該列中檢索員工？
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
uj5u.com熱心網友回復：
如果我正確理解了任務，那么獎金可以重復，對嗎？那么我們必須先將所有員工的獎金匯總起來，然后從大到小排序，得到串列中的第一個
--- 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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/486205.html
上一篇：如何自己除以表的子集，即規范化（t！=0行t=0行）
下一篇：SQL將行轉換為列