SQL查询水平记录

Question

在我的例子中，我在Microsoft SQL Server2012数据库中有一个USER表和一个POST表。

使用PostId的UserTable

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一无所知；

谢谢

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