c# - 使用一个 SQL 表的三向连接

Question

假设有一个 MSSQL 表 UserPost，它代表用户发布的内容，具有以下字段:

编号 |添加日期 |父职位编号 |帖子正文

系统中的用户可以创建一个请求，接收一个响应，然后其他用户可以对响应评论 。即，请求 <=1:many=> 响应 <=1:many=> 评论(想想 StackOverlow 的问题 > 答案 > 评论模型)。

所有用户帖子(Request、Response 和Comment)都由 UserPost 行表示，其中 Request 有parentPostID = null;; Responses的parentPostID是Request的ID，Comment的parentPostID是Response的ID。

我需要以简单的方式输出所有内容:

Request 1
- Response A
-- Comment (i)
-- Comment (ii)
- Response B
-- Comment (i)
Request 2
...

问题:哪个SQL语句以最有用的方式返回所需的信息？

我正在努力编写 (UserPosts) 作为请求 [join] (UserPosts) 作为响应 [join] (UsersPosts) 作为评论之间的三向连接，但我不确定这是最简单的方法。

奖励:是否可以使用 C# Linq 执行此操作？

Answer 1

想不出在 LINQ 中执行此操作的方法。我删除了未使用的列。幸运的是，这是一个有界层次结构。我正在使用新的 hierarchyid 数据类型，它具有所需的排序顺序:

create table UserPosts (
    ID int not null,
    ParentID int null
)
go
insert into UserPosts (ID,ParentID)
select 1,null union all
select 2,null union all
select 3,1 union all
select 4,2 union all
select 5,3 union all
select 6,1 union all
select 7,6
go
select
    *
from
    UserPosts up
        left join
    UserPosts up_1st
        on
            up.ParentID = up_1st.ID
        left join
    UserPosts up_2nd
        on
            up_1st.ParentID = up_2nd.ID
order by
    CONVERT(hierarchyid,
    COALESCE('/' + CONVERT(varchar(10),up_2nd.ID),'') +
    COALESCE('/' + CONVERT(varchar(10),up_1st.ID),'') +
    '/' + CONVERT(varchar(10),up.ID) + '/'
    )

HierarchyID(作为字符串)看起来像 /GrandParent/Parent/Child/ - 所以我们构建看起来像这样的值。显然，如果我们没有祖 parent (up_2nd.ID 为 null，因为我们无法实现所述的 2 个左连接)，那么我们只想构造 /Parent/Child/ - 这就是 1st COALESCE 帮助我们实现的目标。类似地，如果我们找不到任何 parent (up_1st.ID 和 up_2nd.ID 都为空)，那么两个 COALESCE 都会变成空字符串，我们最终构建/ID/。

---

您可以添加:

CASE
    WHEN up_2nd.ID is not null then 'Comment'
    WHEN up_1st.ID is not null then 'Response'
    ELSE 'Request'
END as Level

到你的选择列表，如果你想跟踪项目的级别(或者如果需要，使用数字代替)