SQLServer图数据库一些优点

FROM dbo.ForumPosts ReplyPost,Reply_To,dbo.ForumPosts RepliedPost

WHERE MATCH(ReplyPost-(Reply_To)->RepliedPost)

<p align="left">在这个语句中我们统计了每一篇回复的数量，但是仅仅在一个层面中，并不是在整个回复的树结构里面。

FROM dbo.ForumPosts Post1

WHERE $node_id not <span style="color: #0000ff">in (<span style="color: #0000ff">select $from_id <span style="color: #0000ff">from dbo.Reply_To

<p align="left">MATCH语法只是允许我们关联三个或者更多的实体（比如两个节点和一个关系）。当我们只想关联其中两个的时候，只需要一个常规的连接或者子查询。如上面的语句一样。

 with root ( <span style="color: #0000ff">select $node_id <span style="color: #0000ff">as<span style="color: #000000"> node_id,RootPosts.PostId,RootPosts.PostTitle,<span style="color: #800080">1 <span style="color: #0000ff">as Level,<span style="color: #800080">0 <span style="color: #0000ff">as<span style="color: #000000"> ReplyTo

 </span><span style="color: #0000ff"&gt;from</span><span style="color: #000000"&gt; dbo.ForumPosts RootPosts

 </span><span style="color: #0000ff"&gt;where</span> $node_id not <span style="color: #0000ff"&gt;in</span> (<span style="color: #0000ff"&gt;select</span> $from_id <span style="color: #0000ff"&gt;from</span><span style="color: #000000"&gt; dbo.reply_to)
union all
 </span><span style="color: #0000ff"&gt;select</span><span style="color: #000000"&gt; $node_id,ReplyPost.PostId,ReplyPost.PostTitle,Level</span>+<span style="color: #800080"&gt;1</span> <span style="color: #0000ff"&gt;as</span> [Level],root.PostId <span style="color: #0000ff"&gt;as</span><span style="color: #000000"&gt; ReplyTo

 </span><span style="color: #0000ff"&gt;from</span><span style="color: #000000"&gt; dbo.ForumPosts ReplyPost,reply_to,root

 </span><span style="color: #0000ff"&gt;where</span> ReplyPost.$node_id=<span style="color: #000000"&gt;reply_to.$from_id

       and root.node_id</span>=<span style="color: #000000"&gt;reply_to.$to_id
)
<span style="color: #0000ff">select<span style="color: #000000"> PostId,PostTitle,Level,ReplyTo
<span style="color: #0000ff">from root

<p align="left">?

 with root ( <span style="color: #0000ff">select $node_id <span style="color: #0000ff">as<span style="color: #000000"> node_id,<span style="color: #800080">0 <span style="color: #0000ff">as<span style="color: #000000"> ReplyTo

 </span><span style="color: #0000ff"&gt;from</span><span style="color: #000000"&gt; dbo.ForumPosts RootPosts

 </span><span style="color: #0000ff"&gt;where</span> PostId=<span style="color: #800080"&gt;1</span><span style="color: #000000"&gt;  
union all
 </span><span style="color: #0000ff"&gt;select</span><span style="color: #000000"&gt; $node_id,ReplyTo
<span style="color: #0000ff">from root

<p align="left">我们也可以反过来做，在树状结构中按顺序检索所有父贴。由于CTE不支持OUTER join，所以要在外部添加，代码如下：

with root ( <span style="color: #0000ff">select LeafPost.$node_id <span style="color: #0000ff">as<span style="color: #000000"> node_id,LeafPost.PostId,LeafPost.PostTitle

 </span><span style="color: #0000ff"&gt;from</span><span style="color: #000000"&gt; dbo.ForumPosts LeafPost

 </span><span style="color: #0000ff"&gt;where</span> LeafPost.PostId=<span style="color: #800080"&gt;3</span>  --<span style="color: #000000"&gt; Single post
union all
 </span><span style="color: #0000ff"&gt;select</span> RepliedPost.$node_id <span style="color: #0000ff"&gt;as</span><span style="color: #000000"&gt; node_id,RepliedPost.PostId,RepliedPost.PostTitle

 </span><span style="color: #0000ff"&gt;from</span><span style="color: #000000"&gt; dbo.ForumPosts RepliedPost,Reply_to,root

 </span><span style="color: #0000ff"&gt;where</span> root.node_id=<span style="color: #000000"&gt;Reply_to.$from_id

       and Reply_to.$to_id</span>=<span style="color: #000000"&gt;RepliedPost.$node_id
)
<span style="color: #0000ff">select<span style="color: #000000"> root.PostId,root.PostTitle,RepliedPost.PostId ParentPostId
<span style="color: #0000ff">from<span style="color: #000000"> root
left join reply_to
   on root.node_id</span>=<span style="color: #000000"&gt;reply_to.$from_id
left join dbo.ForumPosts RepliedPost
   on reply_to.$to_id</span>=RepliedPost.$node_id</pre>

检索一个用户所有帖子

--SELECT distinct RepliedPost.PostID,RepliedPost.PostBody
FROM dbo.ForumPosts ReplyPost,dbo.ForumMembers Members,Written_By
WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)-><span style="color: #000000">RepliedPost)
and Members.MemberName=<span style="color: #800000">'<span style="color: #800000">Peter<span style="color: #800000">'
--<span style="color: #000000"> Peter发的所有帖子
SELECT ReplyPost.PostID,ReplyPost.PostBody,RepliedPost.PostId ReplyTo
FROM dbo.ForumPosts ReplyPost,Written_By
WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)-><span style="color: #000000">RepliedPost)
and Members.MemberName=<span style="color: #800000">'<span style="color: #800000">Peter<span style="color: #800000">'

<p align="left">或许你注意到上面两个查询的不同，就是在展示字段上是否使用DISTINCT。这个去重是因为Peter回复同一个帖子可以超过一次。

--SELECT Post.PostID,Post.PostTitle,Member.MemberName

FROM   dbo.ForumPosts Post,Likes,dbo.ForumMembers Member
WHERE MATCH(Member-(Likes)-><span style="color: #000000">Post)
--<span style="color: #000000"> 点赞的人或者被人点赞
SELECT Member.MemberId,Member.MemberName LikeMember,LikedMember.MemberName LikedMemberName
FROM dbo.ForumMembers Member,dbo.ForumMembers LikedMember
WHERE MATCH(Member-(Likes)->LikedMember)

<p align="left">还可以很容易地聚合信息，以获得每个帖子或每个成员的总的Likes。

--<span style="color: #0000ff">select<span style="color: #000000"> Post.PostId,count(*<span style="color: #000000">) totalLikes

<span style="color: #0000ff">from<span style="color: #000000"> dbo.ForumPosts Post,dbo.ForumMembers Members
<span style="color: #0000ff">where Match(Members-(Likes)-><span style="color: #000000">Post)
group by PostId,PostTitle
--每个成员总的点赞数<span style="color: #0000ff">select<span style="color: #000000"> LikedMembers.MemberId,LikedMembers.MemberName,count(*<span style="color: #000000">) totalLikes
<span style="color: #0000ff">from<span style="color: #000000"> dbo.ForumMembers Members,dbo.ForumMembers LikedMembers
<span style="color: #0000ff">where Match(Members-(Likes)-><span style="color: #000000">LikedMembers)
group by LikedMembers.MemberId,LikedMembers.MemberName

<p align="left">用户点赞并且回复帖子

FROM dbo.ForumPosts LikedPost,dbo.ForumPosts ReplyPost,dbo.ForumMembers Member,Written_By

WHERE MATCH(Member-(Likes)->LikedPost<-(Reply_To)-ReplyPost-(Written_By)->Member)

<p align="left">注意，对于‘Member’节点使用了两次在同一个MATCH表达式中。这形成了一种过滤：点赞并且有回复的成员，需要在‘LikedPost’和‘ReplyPost’中都有记录才可以。

<span style="color: #0000ff">from<span style="color: #000000"> Forum.Likes Likes,Forum.ForumPosts Posts,Forum.ForumMembers Members

<span style="color: #0000ff">where Likes.MemberId=<span style="color: #000000">Posts.OwnerId
and Posts.ReplyTo=<span style="color: #000000">Likes.PostId
and Members.MemberId=Likes.MemberId

<p align="left">看起来这种写法更难理解和读懂。

FROM   dbo.ForumPosts ReplyPost,Written_By,dbo.ForumMembers Members

WHERE  MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)-><span style="color: #000000">RepliedPost)
GROUP BY MemberId,Members.MemberName
Having Count(RepliedPost.PostId) ><span style="color: #800080">1

<p align="left">回帖个一个帖子多次的成员:

*) FROM   dbo.ForumPosts ReplyPost,dbo.ForumMembers Members

WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)-><span style="color: #000000">RepliedPost)
GROUP BY MemberId,MemberName,RepliedPost.PostId
Having count(*) ><span style="color: #800080">1

<p align="left">上述两种语句中唯一的不同就是展示结果的聚合。

　　通过上述构建在图数据模式下的查询和关联，对比了常规语句以及在关系模式下的相同查询，不难发现无论是在易读性，逻辑理解上还是在性能上都有很大提高。当然这只是第一个版本，所以难免有很多问题， 下一篇我讲介绍这个版本存在的一部分问题。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!