mysql - SQL查询水平记录

Question

就我而言，Microsoft SQL Server 2012 数据库中有一个 USER 表和一个 POST 表。

[UserTable] 和 PostId

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

[帖子表]

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