我有两张桌子tblEmployee
和tblTarget
. 这是我的 SQL 查询:
select EmpCode,E.TM_Name, CurrentTarget, CreatedDate,RankOrder,A.PreviousTarget from(
select EmpCode, CurrentTarget, CreatedDate,RankOrder,T.PreviousTarget from (
Select
EmpCode, CurrentTarget, CreatedDate,PreviousTarget,
Rank() over (partition by EmpCode order by CreatedDate desc) RankOrder
From tbl_Target
) T WHERE RankOrder=1)A
left join tblEmployee E on A.EmpCode=E.TM_Emp_Id
这就是我得到的,它是完美的。
EmpCode TM_Name CurrentTarget CreatedDate RankOrder PreviousTarget
-------------- --------------- ------------- ----------------------- ------------- --------------
20078 kaul 60 2018-07-11 05:05:03.687 1 50
56042 Bob 40 2018-07-11 05:05:03.687 1 20
56079 Alice 30 2018-07-11 05:05:03.703 1 30
56080 Sikon 50 2018-07-11 05:05:03.703 1 40
56094 Ssohy 35 2018-07-11 05:05:03.703 1 20
我只得到那些存在于tblTarget
. 而没有目标的员工不显示。虽然我希望它们以 0CurrentTarget
和 0显示PreviousTarget
。我想要这样的输出:
EmpCode TM_Name CurrentTarget CreatedDate RankOrder PreviousTarget
-------------- --------------- ------------- ----------------------- ------------- --------------
20078 kaul 60 2018-07-11 05:05:03.687 1 50
56042 Bob 40 2018-07-11 05:05:03.687 1 20
56079 Alice 30 2018-07-11 05:05:03.703 1 30
56080 Sikon 50 2018-07-11 05:05:03.703 1 40
56094 Ssohy 35 2018-07-11 05:05:03.703 1 20
101 Joe 0 2018-08-12 05:05:03.687 1 0
102 John 0 2018-08-12 05:05:03.687 1 0
103 Sid 0 2018-08-12 05:05:03.687 1 0
104 Manous 0 2018-08-12 05:05:03.687 1 0
我怎么做?
您需要更改left-join
. 您需要所有员工,即使是那些在目标中没有匹配行的员工,因此您可以切换表格的顺序并使Employee
表格成为左边的表格(我在下面的代码中做了),或者您将联接更改为一个right join
:
select EmpCode, e.TM_Name, isnull(CurrentTarget, 0) CurrentTarget, CreatedDate, RankOrder, isnull(A.PreviousTarget, 0) PreviousTarget
from tblEmployee e
left join
(
select EmpCode, CurrentTarget, CreatedDate,RankOrder,T.PreviousTarget
from (
Select EmpCode, CurrentTarget, CreatedDate,PreviousTarget, Rank() over (partition by EmpCode order by CreatedDate desc) RankOrder
From tbl_Target
) T WHERE RankOrder=1
) A on A.empCode = e.tm_emp_id
当显示 CurrentTarget 和 PreviousTarget 时,由于它们将NULL
用于没有目标的员工,您可以使用ISNULL()函数并返回 0。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句