SQLServer图数据库一些优点

SQLServer图数据库一些优点

背景:

  图数据库对于表现和遍历复杂的实业之间关系是很有效应的。而这一个在守旧的关系型数据库中越发是对此报表来说很难落到实处。假诺把守旧关系型数据库比做火车的话,那么到今天大数目时期,图数据库可比做高铁。它已化作NoSQL中关切度最高,发展趋势最精通的数据库。伴随SQL
Server 20一七的产出,在SQL
Server上边有了专门的图数据库,那么未来急需任何数据库只怕效能低下地处理这一个干活儿,今后是或不是足以让大家轻便的贯彻了那?

  接下去小编会用多个篇幅介绍SQLServer 图数据库以及它的利弊。

SELECT Members.MemberId, Members.MemberName,

         Count(distinct RepliedPost.PostId) as Total

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

         Written_By,dbo.ForumMembers Members

  WHERE  MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

  GROUP BY MemberId, Members.MemberName

  Having Count(RepliedPost.PostId) >1
  1. 这么些查询很相像,当然MATCH的语法更便于精晓。
  2. 举办完上边语句询问结果如下:

小结

  本篇介绍了图数据库的一些简约定义和理解,概述了SQLServer20一柒中什么创立图数据库的着力步骤和语句。那只是1个开端版本必然有数不清缺陷,当然也有一些独到之处,下壹篇笔者将先介绍优点再说一下有怎么样不足。

参考文献:

 

 

  1. 在这一年,可能在关系型形式里面随着关系的充实读取就能够越困难,而在图数据情势中MATCH子句相对就便于很多。让我们看一下在图数据形式中有的有趣又实用的地点。

  轻松定义:图数据库是NoSQL数据库的1系列型,它使用图形理论存款和储蓄实体之间的涉嫌消息。图形数据库是一种非关系型数据库,它应用图形理论存款和储蓄实体之间的关联音讯。最广大例子就是社会网络中人与人里面包车型大巴关系。关系型数据库用于存款和储蓄“关系型”数据的效应并不佳,其查询复杂、缓慢、越过预想,而图片数据库的新鲜设计恰恰弥补了这么些毛病。

  SQL Server
20一七将拉动新的功力之一就是图数据库。图数据库不像关系型数据库在一张“图”内将数据表现为节点,边和品质,而是一种浮泛的数据类型,通过1组顶点节点、点和边来显示关系和连续,就如一个缠结的渔网。使大家用简单的主意来显现和遍历实体间的关联。图对象被用来表示复杂的关系。一层正是三个一定的图,记录如论坛帖子和死灰复然之间的涉及,以及人与人中间的涉及。多层有三个根节点(比如,论坛中的帖子和还原),但是多少个图不必然有根节点(举个例子人们之间的涉嫌)

  本文中,我们共同利用二个论坛数据例子,使用最新的图模型。也会相比图和关系型模型的查询复杂度。

事必躬亲情形

  SQL Server 201七 CTP 2.1下载地址:

  使用SSMS 1七.0,下载地址:

创办模型

  下图是1个关系型实体的模子,以此作为相比:

图片 1

 

  若是想要比较,可以应用上边的台本创设,或许直接创制图模型。不过,供给用SSMS成立二个新的数据库“GraphExample”。代码如下:

create database GraphExample
  go
  -- Trying an entire graph model
  use GraphExample
  go
  create schema Forum
  go
  create table Forum.ForumMembers
  (MemberId int not null primary key Identity(1,1),
  MemberName varchar(100))
  go
  create table Forum.ForumPosts
  ([PostID] int not null primary key,
  PostTitle varchar(100),
  PostBody  varchar(100),
  OwnerID int,
  ReplyTo   int)
  go
  Create table Forum.Likes
  (MemberId int,
  PostId int)
  go
  create table Forum.LikeMember
  (MemberId int,
   LikedMemberId int)
   go
  INSERT Forum.ForumMembers values('Mike'),('Carl'),('Paul'),('Christy'),('Jennifer'),('Charlie')
  go

  INSERT INTO [Forum].[ForumPosts] 
             (
             [PostID]
             ,[PostTitle]
             ,[PostBody],OwnerID, ReplyTo
                   )
       VALUES
           (4,'Geography','Im Christy from USA',4,null),
             (1,'Intro','Hi There This is Carl',2,null)
  INSERT INTO [Forum].[ForumPosts] 
             (
             [PostID]
             ,[PostTitle]
             ,[PostBody],OwnerID, ReplyTo
                   )
       VALUES
          (8,'Intro','nice to see all here!',1,1),
          (7,'Intro','I''m Mike from Argentina',1,1),
           (6,'Re:Geography','I''m Mike from Argentina',1,4),
          (5,'Re:Geography','I''m Jennifer from Brazil',5,4),
                (3,'Re: Intro','Hey Paul This is Christy',4,2),
                   (2,'Intro','Hello I''m Paul',3,1)
  go
  INSERT Forum.Likes VALUES (1,4),
   (2,7),
   (2,8),
   (2,2),
   (4,5),
   (4,6),
   (1,2),
   (3,7),
   (3,8),
       (5,4)
  go
  Insert Forum.LikeMember VALUES (2,1),
   (2,3),
   (4,1),
   (4,5)

 

图模型

  图模型的安顿与关系型模型完全两样。表在图模型中也许是边也许节点。大家须求调节如何表是边,哪些表是节点。

  图具备如下特点:

  • 富含节点和边;
  • 节点上有属性(键值对);
  • 边有名字和自由化,并延续有3个方始节点和2个停止节点;
  • 边也足以有品质。

  下图显示了图模型:

 图片 2

  如图所示,在模型中节点和边很轻巧分明:逻辑模型中的全部实体就是节点,而具有涉及正是边。这里有“Posts”和“Members”几个实体,
‘Reply To’, ‘Like’‘Written By’三个边。

注意

  节点和边可是是富含独特字段的表。未有其余限制禁止大家成立健康的表之间的关联,以便将模型转化为涉及和图模型的结合。

  例如,‘Written By’‘Posts’
‘Members’的关系,能够转账为3个一对多的涉嫌。通过创建1个边的涉嫌表,我们能够用健康的涉及表来表现所谓的图模型中的表。也便是构成形式了。

  当我们创立3个根节点实体,那几个实体接收3个称为‘$node_id’的一个钱打二15个结字段。我们得以行使这几个字段作为主键,SQL
Server
允许总计字段作为主键:倘使那几个主键是1个JSON字段,就不相符作为主键了。因而大家的节点必须包蕴多少个键:业务键,整型字段,以及‘$node_id’
键,包蕴整型字段自增加的JSON键。

  上边为节点实体的脚本:

Use GraphExample

  go

  CREATE TABLE [dbo].[ForumMembers](

         [MemberID] [int] IDENTITY(1,1) NOT NULL,

         [MemberName] [varchar](100) NULL

  )

  AS NODE

  GO



  CREATE TABLE [dbo].[ForumPosts](

         [PostID] [int] NULL,

         [PostTitle] [varchar](100) NULL,

         [PostBody] [varchar](1000) NULL

  )

  AS NODE

 

注意

  在创立对象后,在目的浏览器中反省对象。恐怕此刻专注到一个新的文本夹在‘Tables’文件夹里面叫做‘Graph’。同时也注意到自增字段的名字,就算大家能够用简称来引用这么些字段,比如$node_id,然而真正的字段名称包涵了GUID。那么些简称字段其实是三个假的名字,称之为“伪列”(可以知道为别名),大家能在询问中接纳。

图片 3

 

  如图,插入数据到节点表:大家只必要忽略$node_id,写出插入此外字段的说话就可以,语句如下:

INSERT ForumMembers values ('Mike'),('Carl'),('Paul'),('Christy'),('Jennifer'),('Charlie')
  INSERT INTO [dbo].[ForumPosts]

             (

             [PostID]

             ,[PostTitle]

             ,[PostBody]

                   )

       VALUES

          (8,'Intro','nice to see all here!'),

          (7,'Intro','I''m Mike from Argentina'),

           (6,'Re:Geography','I''m Mike from Argentina'),

          (5,'Re:Geography','I''m Jennifer from Brazil'),

           (4,'Geography','Im Christy from USA'),

                (3,'Re: Intro','Hey Paul This is Christy'),

             (1,'Intro','Hi There This is Carl')

                   (2,'Intro','Hello I''m Paul')

选拔查询语句能够看来ForumPosts表的结果。你会发觉$node_id字段,是一个JSON字段包括了实体类型和三个自增整型ID,它正是自增加ID。

 图片 4

制造边表

  这些操作非常粗大略,边表有质量,属性就是表中的平常字段。脚本如下:

Create table dbo.[Written_By]

  as EDGE

  CREATE TABLE [dbo].[Likes]

  AS EDGE

  CREATE TABLE [dbo].[Reply_To]

  AS EDGE

  每一种边表有三个伪列,我们供给管理:

  • $edge_id: 边记录的ID
  • $from_id:在边中著录的节点ID
  • $to_id:在边中著录的别样节点ID

    注意那么些定义,最为根本的某个正是:大家必要用一种合乎逻辑的方法定义
 $to_id and $from_id
字段对于每条边表示什么样?你能够观测以前定义的边表怎么样定义的边,那是壹种双向的客观选用,使得大家更易于选取和清楚。

以下是大家的合理定义:

Written_By:

$from_id will be the post

$to_id will be the member

Likes:

$from_id will be who likes

$to_id will be who/what is liked

Reply_To:

$from_id will be the reply to the main post

$to_id  will be the main post

那么些采用未有技术限制,但大家需求在插入新记录时保留它们,恒久不要混淆关系的每一方的含义。

注意

  除了七个伪列以外,全部的表表都有额外字段,并且全是隐藏字段。大家能够在字段属性中看看隐匿的概念,并且这么些隐藏字段不会冒出在查询结果中。

图片 5

 

图片 6

 

安顿边记录

    插入边表的言语需求边的两端ID,$From_id and
$To_id。这几个字段必要用$node_id的值来填充。比方,对于三个帖子的分子,‘Written_By’包含post
$node_id 作为$From_id
并且有member的$node_id作为$To_id字段。

上边是插入语句:

Insert into Written_By ($to_id,$from_id) values

   (

   (select $node_id from dbo.ForumMembers where MemberId= 1 ),

   (select $node_id from dbo.ForumPosts where PostID=8 )

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId=1  ),

   (select $node_id from dbo.ForumPosts where PostID=7 )

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId= 1 ),

   (select $node_id from dbo.ForumPosts where PostID= 6)

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId=5  ),

   (select $node_id from dbo.ForumPosts where PostID=5 )

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId=4  ),

   (select $node_id from dbo.ForumPosts where PostID=4 )

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId=3  ),

   (select $node_id from dbo.ForumPosts where PostID=3 )

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId=3  ),

   (select $node_id from dbo.ForumPosts where PostID=1 )

   ),

   (

   (select $node_id from dbo.ForumMembers where MemberId=3  ),

   (select $node_id from dbo.ForumPosts where PostID=2 )

   )

 

注意

  那样插入是否感到很勤奋?以往我们得以动用七个对象框架用以援助图对象,近来还不援救这些效应。

  插入Reply_To脚本如下:

 INSERT Reply_To ($to_id,$from_id) 
   VALUES
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),
         (SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 6)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 1),
         (SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 7)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 1),
         (SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 8)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 1),
         (SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 2)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),
         (SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 5)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 2),
(SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 3))

最后,再插入Likes:

 

INSERT Likes ($to_id,$from_id) 
   VALUES
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 7),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 8),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 2),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 5),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 4)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 6),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 4)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 2),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 7),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 3)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 8),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 3)),
   ((SELECT $node_id FROM dbo.ForumPosts WHERE PostID = 4),
         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 5))

 

Likes
边很好的求证了边的功用效应。仅仅插入多少个menbers和post表的涉嫌,不过大家能够规定在使用中成员也恐怕喜欢另二个分子。当然,大家也能用这么些边去关联那个成员和其余成员的涉嫌。在关系型模型中大家须要四个表完毕那一个操作,在图数据库大家只供给1个边。

上面大家在论坛的分子之间插入越多的Like:

INSERT Likes ($to_id,$from_id)

   VALUES

   ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1),

         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),

   ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 3),

         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 2)),

   ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 1),

         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 4)),

   ((SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 5),

         (SELECT $node_id FROM dbo.ForumMembers WHERE MemberID = 4))
with root as

  ( select LeafPost.$node_id as node_id,LeafPost.PostId,

           LeafPost.PostTitle

     from dbo.ForumPosts LeafPost

     where LeafPost.PostId=3  -- Single post

  union all

     select RepliedPost.$node_id as node_id,RepliedPost.PostId,

            RepliedPost.PostTitle

     from dbo.ForumPosts RepliedPost, Reply_to, root

     where root.node_id=Reply_to.$from_id

           and Reply_to.$to_id=RepliedPost.$node_id

  )

  select root.PostId,root.PostTitle,

         RepliedPost.PostId ParentPostId

  from root

  left join reply_to

       on root.node_id=reply_to.$from_id

  left join dbo.ForumPosts RepliedPost

       on reply_to.$to_id=RepliedPost.$node_id

 

介绍:

用户点赞并且苏醒帖子

查找七个帖子中的全数回复

上述二种语句中无与伦比的区别正是突显结果的集纳。

 

--  Peter回复的所有帖子

   SELECT distinct RepliedPost.PostID,RepliedPost.PostTitle,

           RepliedPost.PostBody

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

        dbo.ForumMembers Members,Written_By

   WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

   and Members.MemberName='Peter'

  -- Peter发的所有帖子

   SELECT ReplyPost.PostID,ReplyPost.PostTitle,ReplyPost.PostBody,

          RepliedPost.PostId ReplyTo

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

        dbo.ForumMembers Members,Written_By

   WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

   and Members.MemberName='Peter'
  1. 在WHERE
    子句中,我们须求关联全体的表,用上面那种MATCH语句来促成关系:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost

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

回帖个多少个帖子数次的分子:

  1. 还紧缺回复对象的名字。像上边同样扩展‘ForumMembers’
     ‘Written_By’在FROM子句中:

    From dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,

    dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By,

    dbo.ForumMembers ReplyMember, Written_By ReplyWritten_By

  1. 那几个语法很有意思:“-”破折号表示边的$From_id字段表示关系,然后“->”破折号和大于号用边的$To_id字段表示关系。
  2. 因为驾驭那多少个外号有reply,这几个外号有replied
    post,大家得以创设3个询问字段列表:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost

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

 

 

-- Posts 、members 和replies

   SELECT RepliedPost.PostId,RepliedPost.PostTitle,RepliedMember.MemberName,

   ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle

      FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,

        dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By

   WHERE MATCH(ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)
FROM dbo.ForumPosts ReplyPost, dbo.ForumPosts RepliedPost
 with root as

  ( select $node_id as node_id,RootPosts.PostId,RootPosts.PostTitle,

           1 as Level, 0 as ReplyTo

     from dbo.ForumPosts RootPosts

     where PostId=1  

  union all

     select $node_id,ReplyPost.PostId, ReplyPost.PostTitle,

            Level+1 as [Level],root.PostId as ReplyTo

     from dbo.ForumPosts ReplyPost, reply_to, root

     where ReplyPost.$node_id=reply_to.$from_id

           and root.node_id=reply_to.$to_id

  )

  select PostId,PostTitle, Level, ReplyTo

  from root

 

 图片 7

找出二个用户拥有帖子

    上一篇简介了图数据库的有的主旨内容(初识SQL Server2017图数据库(1)),本篇通过比较关系型一些语法来体现图数据库格局的有个别优点,比方查询方便,语句易掌握等。

 

 

SELECT Members.MemberId, Members.MemberName,

         RepliedPost.PostId RepliedId,count(*) as TotalReplies

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

       Written_By,dbo.ForumMembers Members

  WHERE MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

  GROUP BY MemberId,MemberName,RepliedPost.PostId

  Having count(*) >1
with root as

  ( select LeafPost.$node_id as node_id,LeafPost.PostId,

           LeafPost.PostTitle

     from dbo.ForumPosts LeafPost

     where LeafPost.PostId=3  -- Single post

  union all

     select RepliedPost.$node_id as node_id,RepliedPost.PostId,

            RepliedPost.PostTitle

     from dbo.ForumPosts RepliedPost, Reply_to, root

     where root.node_id=Reply_to.$from_id

           and Reply_to.$to_id=RepliedPost.$node_id

  )

  select root.PostId,root.PostTitle,

         RepliedPost.PostId ParentPostId

  from root

  left join reply_to

       on root.node_id=reply_to.$from_id

  left join dbo.ForumPosts RepliedPost

       on reply_to.$to_id=RepliedPost.$node_id

注意,对于‘Member’节点使用了四回在同二个MATCH表明式中。那造成了1种过滤:点赞并且有借尸还魂的成员,须求在‘LikedPost’和‘ReplyPost’中都有记录才得以。

FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost
WHERE MATCH(ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)

MATCH语法只是同意大家关系多少个也许越来越多的实体(比方八个节点和多少个涉及)。当我们只想关联在这之中几个的时候,只必要二个健康的一而再只怕子查询。如上面包车型客车说话同样。

根贴(主贴)的列表

查询3个用全体的音讯,与帖子区别,那没有必要树,要轻便不少:

  1. 纵然我们能选拔其余外号,不过在拍卖图对象时最佳选拔有意义的名字。
  2. 我们供给“posts”之间的关系,而那么些涉及便是表Reply_to’。语法如下:
SELECT Members.MemberId, Members.MemberName,

         Count(distinct RepliedPost.PostId) as Total

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

         Written_By,dbo.ForumMembers Members

  WHERE  MATCH(Members<-(Written_By)-ReplyPost-(Reply_To)->RepliedPost)

  GROUP BY MemberId, Members.MemberName

  Having Count(RepliedPost.PostId) >1
  1. 那就能够在SELECT列表中增多回帖人的名字,最后的询问如下:

    

总结每篇帖子的回复数

总结每篇帖子的回复数

 

 图片 8

回帖给三个帖子的分子

 图片 9

咱俩因此上边不行使MATCH的言辞获得全体的根贴:

 

 

 

SELECT Post1.PostId,Post1.PostTitle

  FROM dbo.ForumPosts Post1

  WHERE $node_id not in (select $from_id from dbo.Reply_To
FROM dbo.ForumPosts ReplyPost, dbo.ForumPosts RepliedPost
  1. 接下来,修改MATCH子句,‘ReplyMember’亟待关联‘ReplyPost’,唯独怎么着去管理那个关系而不影响其它关系?须求用分歧的主意来贯彻:

    WHERE MATCH(ReplyMember<-(ReplyWritten_By)-ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)

 

 

  1. 在关系型模型中壹律效果的询问如下:

那就是说在关系型形式中代码如下:

累加一个‘Level’字段,展现树结构。在T-SQL中有三个简便的语法,叫做CTE达成递归。可是有叁个难题,不可能选取MATCH语法在贰个派生表上,此时能够使用CTE。假如有至关重要,能够在CTE中应用MATCH,但是反之就不行了,有诸如此类的范围。下边显示一下选用正规的涉嫌仅仅使用CTE来迭代,代码如下:

  1. 还缺少回复对象的名字。像下边同样扩充‘ForumMembers’
     ‘Written_By’在FROM子句中:

    From dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,

    dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By,

    dbo.ForumMembers ReplyMember, Written_By ReplyWritten_By

  SELECT distinct RepliedPost.PostID,RepliedPost.PostTitle,

          RepliedPost.PostBody,

          count(ReplyPost.PostID) over(partition by RepliedPost.PostID)

              as TotalReplies

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

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

行使CTE递归语法,大家得以用一种树结构检索叁个帖子的全数回复。假如选取正规的语法不可能在搜索帖子一的时等候检查索贴子三,因为3是对贰的复苏,而二是对壹的还原。使用CTE.当查询帖子一的具有回复时能寻觅贴子3。代码如下:

笔者们也得以成立一些更加有意思的询问,举例,查找这个点赞并回复的人,如下:

  SELECT distinct RepliedPost.PostID,RepliedPost.PostTitle,

          RepliedPost.PostBody,

          count(ReplyPost.PostID) over(partition by RepliedPost.PostID)

              as TotalReplies

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

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

还是能够很轻巧地集合消息,以博取每种帖子或每一个成员的总的Likes。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图