SQL查询水平记录

Question

在我的情况下，我在Microsoft SQL Server 2012数据库中有USER表和POST表。

[用户表]与帖子ID

uid  name  postId 
-------------------------- 
1  John  1 
2  Peter  2 
3  Susan  2 
4  Ben   3 
5  Ken   4 
6  Mary  5 

[PostTable]

postId postTitle managerPostId 
------------------------------------- 
1  AO   2 
2  SSM   3 
3  CSM   [null] 
4  AP   5 
5  SA   6 
6  PM   [null] 

，这是我预期的结果

uid name postTitle manager1 mgrPostTitle1 manager2 mgrPostTitle2 
---------------------------------------------------------------------------------------- 
1 John AO   Peter  SSM    Susan  SSM 
2 Peter SSM   Ben   CSM    [null]  [null] 
3 Susan SSM   Ben   CSM    [null]  [null] 
4 Ben  CSM   [null]  [null]   [null]  [null] 
5 Ken  AP   Mary  SA    [null]  [null] 
6 Mary SA   [null]  [null]   [null]  [null] 

我怎样才能实现这个结果？我试过Pivot，但我不知道动态postId;

THANKS

Answer 1

尝试使用若干个连接，像这样：

select u.uid, u.name, p.postTitle, u2.name as manager1, p2.posttitle as mgrPostTitle1, u3.name as manager2, p3.posttitle as mgrPostTitle2 
from User u 
Inner join post p on p.postid = u.postid 
left join user u2 on u2.uid = p.managerpostid 
left join post p2 on p2.postid = u2.postid 
left join user u3 on u3.uid = p2.managerpostid 
left join post p3 on p3.postid = u3.postid