问题描述

SQLServer一对多匹配问题

大神们好，小弟请教一个问题：

Create Table #T1(Id int, Name varchar(20), DeptId varchar(20))
Insert into #T1(Id, Name, DeptId)
select Id=1, Name = '张三', DeptId = '001.001.001'
union
select Id=2, Name = '李四', DeptId = '001.001.002'
union
select Id=3, Name = '王五', DeptId = '001.002.002'

Create Table #T2(Id int identity(1, 1), DeptId varchar(20), Amount money)
Insert Into #T2(DeptId, Amount)
select DeptId = '001', Amount = 1000
union
select DeptId = '001.001', Amount = 500

-- 这里如何才能更简便的返回以下结果
/*
Id  Name    DeptId  Amount
1   张三  001.001.001 500.00
2   李四  001.001.002 500.00
3   王五  001.002.002 1000.00
*/
select T1.Id, T1.Name, T1.DeptId, T2.Amount
from #T1 T1
left join #T2 T2 on T1.DeptId like T2.DeptId + '%'

drop table #T1
drop table #T2

解决方案

哎~~~，贴代码

select Id, Name, DeptId, Amount
from (
    select row_number() over( partition by T1.Id order by T2.DeptId desc) as Num,
        T1.Id, T1.Name, T1.DeptId, T2.Amount
    from #T1 T1
    left join #T2 T2 on T1.DeptId like T2.DeptId + '%'
    ) T
where T.Num=1