Server :连接4-5个不同结构的表
Server :连接4-5个不同结构的表
提问于 2017-02-23 05:36:57
我有三张桌子
- 员工
- ApprovalMatrix
- ExpenseMatrix
Employee包含：
ID CompanyId Name
---------------------------------------
9 1 ABC
10 1 XYZ
11 1 WEW ApprovalMatrix包含：
ID Module Employeeid EmployeeLeaderId
--------------------------------------------
11 M1 9 11
12 M1 10 11
13 M2 9 11
55 M3 10 11
56 M2 10 11ExpenseMatrix包含：
ID Employeeid EmployeeLeaderId
--------------------------------------------
11 10 9
12 11 9 预期结果：
Module EmployeeName EmployeeLeaderName
--------------------------------------------
M1 ABC WEW
M1 XYX WEW
M2 ABC WEW
M3 XYZ WEW
M2 XYZ WEW
Expense XYZ ABC
Expense WEW ABC就像这样，我有4-5个不同的表需要合并所有的表雇员，也需要模块名称哪个模块的领导被分配。
下面是我已经尝试过的查询
select
C.Module, A.Employeeid, B.Name
from
Employee B
join
ExpenseMatrix A on A.EmployeeLeaderId = B.Id
join
ApprovalMatrix C on C.EmployeeLeaderId = B.Id
where
B.EmpStatus = 1 and A.EmployeeId = 56回答 2
Stack Overflow用户
回答已采纳
发布于 2017-02-23 05:49:54
select
A.Module, e1.name, e2.Name
from
ApprovalMatrix AM
inner join Employee E1
on AM.EmployeeId = E1.ID
inner join Employee E2
on AM.EmployeeLeaderId = E2.ID
union all
select 'Expense', e1.name, e2.name
From ExpenseMatrix EM
inner join Employee E1
on EM.EmployeeId = E1.ID
inner join Employee E2
on EM.EmployeeLeaderId = E2.IDStack Overflow用户
发布于 2017-02-23 05:55:05
嗨，试试下面的代码。
;with temp as
(select Module , Employeeid , EmployeeLeaderId from ApprovalMatrix
union all
select 'Expence' as Module ,Employeeid , EmployeeLeaderId from ExpenseMatrix )
select Module, b.Name as EmployeeName ,c.name as EmployeeLeaderName from temp a
left join Employee b on a.EmployeeLeaderId =b.Id
left join Employee c on a.EmployeeLeaderId =c.id注意:在union中添加更多的表，全部在CTE块中。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/42407900
复制相关文章
相似问题
腾讯云开发者
