declare @emp table
(
EmployeeId int, CompanyId int ,FirstName nvarchar(50),MiddleName nvarchar(50) ,LastName nvarchar(50)
)
insert into @emp select 1,1,'rahul','kumar','Sharma'
insert into @emp select 16,1,'nitin','','Sharma'
select * From @emp
declare @PayInformation table
(
EmployeeId int ,IsHourly bit ,PayFrequency nvarchar(50) ,Amount decimal(18,2),StandardHours decimal(18,2) ,Year int,Sequence int
)
insert into @PayInformation select 1,0,'monthly',40.00,40,2013,1
insert into @PayInformation select 1,0,'monthly',100.00,40,2013,2
insert into @PayInformation select 16,0,'monthly',100.00,40,2013,2
select * From @PayInformation
select * from @emp as e
inner join @PayInformation as p ON e.EmployeeId=p.EmployeeId
这个连接语句给了我 3 行,因为EmployeeId
1 在表中有 2 PayInformation
行。 但是我想只加入具有最大序列号的行。 因此,根据我想要的结果,它应该与员工 2 的序列 1 号连接。
有
几种方法可以做到这一点
第一:
select *
from @emp as e
outer apply (
select top 1 t.*
from @PayInformation as t
where t.EmployeeId=e.EmployeeId
order by t.Sequence desc
) as p
第二:
select *
from @emp as e
left outer join @PayInformation as p on p.EmployeeId=e.EmployeeId
where
exists (
select 1
from @PayInformation as t
where t.EmployeeId=e.EmployeeId
having max(t.Sequence) = p.Sequence
)
第三
;with cte_PayInformation as (
select *, row_number() over(partition by EmployeeId order by Sequence desc) as rn
from @PayInformation
)
select *
from @emp as e
left outer join cte_PayInformation as p on p.EmployeeId = e.EmployeeId and p.rn = 1
SQL 小提琴演示
快速说明 - 这些查询并不等效,如果表中有重复Sequence, EmployeeId
@PayInformation
第二个查询可能会返回更多行。