大神们好,小弟请教一个问题:
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