SQLServer图数据库一些优点,使用子查询和联结表

    上1篇简介了图数据库的局地主干内容(初识SQL Server20一七图数据库(壹)),本篇通过对照关系型一些语法来展现图数据库形式的有的优点,举例查询方便,语句易理解等。

    上壹篇简单介绍了图数据库的一些中坚内容(初识SQL Server20壹七图数据库(壹)),本篇通过对照关系型一些语法来反映图数据库形式的①对亮点,举个例子查询方便,语句易掌握等。

MySQL是一个关系型数据库管理种类,由瑞典MySQL
AB 公司支付,近期属于 Oracle 旗下产品。MySQL
最盛行的关系型数据库管理连串,在 WEB 应用方面MySQL是最棒的 奥迪Q伍DBMS
(Relational Database Management
System,关周详据库管理种类)
应用程式之一。

为了演示触发器,下边例子分别落成插入,删除触发器。

在图数据库模型上创设查询的优势:

在图数据库模型上营造查询的优势:

MySQL是1种关系数据库管理种类,关联数据库将数据保存在分歧的表中,而不是将具备数据放在三个大仓库内,这样就大增了进程并巩固了灵活性。

示范以前,先创建五个表,关关联起来。

T-SQL
带给图表查询部分新的语法。在SELECT语句中大家有1部分出奇的语句来关联点和边。让咱们来排练一些,创设查询语句检索发帖和死灰复燃,如下:

T-SQL
带给图表查询部分新的语法。在SELECT语句中大家有部分差别平常的语句来关联点和边。让大家来排练一些,创设查询语句检索发帖和死灰复燃,如下:

MySQL所运用的 SQL 语言是用以访问数据库的最常用口径语言。MySQL
软件应用了双授权政策,它分成社区版和商业版,由于其容积小、速度快、总体具备开支低,特别是开放源码那壹特征,一般中型小型型网址的支付都选用MySQL
作为网址数据库。由于其社区版的习性特出,搭配
PHP 和 Apache 可组合杰出的开拓条件。

997755.com澳门葡京 1997755.com澳门葡京 2代码

  1. 我们搜索各种记录的多少个部分,发帖和死灰复燃,因而大家必要在FROM子句中引用四回ForumPosts’表,本条地点能够选拔局地有意义的外号:
  1. 咱俩寻觅各样记录的三个部分,发帖和恢复生机,由此我们须求在FROM子句中援引三遍ForumPosts’表,这么些地点可以应用局地有含义的别称:

997755.com澳门葡京 3

–创设一个表[Topic]
CREATE TABLE dbo.Topic
(
    TopicId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Title NVARCHAR(300) NOT NULL,
    Content NVARCHAR(2000) NOT NULL,
    Author NVARCHAR(50) NOT NULL,
    Reply INT DEFAULT(0) NOT NULL    
)
GO

    

    

使用子查询

查询:任何SQL语句都以询问。但此术语一般指SELECT语句。SQL还同意成立子查询,即嵌套在任何查询中的查询。

 

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

1、利用子查询实行过滤

譬如说:列出订购物品TNT二的具备客户
手续:(壹)检索包罗物品TNT二的享有订单的号码。
(贰)检索具备前一步骤列出的订单号码的有所客户的ID。
(3)检索前一步骤重回的具备客户ID的客户音讯。

SELECT order_num FROM orderitems WHERE prod_id='TNT2';

此语句的出口如下图所示:

997755.com澳门葡京 4

SELECT cust_id FROM orders WHERE order_num IN (20005,20007);

此语句的出口如下图所示:

997755.com澳门葡京 5

明天,把第二个查询(再次来到订单号的这一个)变为子查询组合多少个查询,如下所示:

SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2');

此语句的出口如下图所示:

997755.com澳门葡京 6

近日获取了订货货品TNT2的享有客户的ID。下一步是探求那些客户ID的客户音信。检索两列的SQL语句为:
如下所示:

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (10001,10004);

此语句的出口如下图所示:

997755.com澳门葡京 7

末段的查询语句如下所示:

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN (SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id='TNT2'));

此语句的出口如下图所示:

997755.com澳门葡京 8

其它一个表,

 

 

贰、作为计算字段使用子查询

使用子查询的另一艺术是开创计算字段。比如:呈现customers表中各个客户的订单总额。订单与相应的客户ID存储在orders表中。
手续:(一)在customers表中寻找客户列表。
(贰)对于检索出来的每一个客户,总结其在orders表中的订单数量。
为了对各类客户执行COUNT()计算,应该将COUNT()作为三个子查询。代码如下:

SELECT cust_name,cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id) AS orders FROM customers ORDER BY cust_name;

此语句的输出如下图所示:

997755.com澳门葡京 9

997755.com澳门葡京 10997755.com澳门葡京 11代码

  1. 固然我们能选用任何别称,不过在拍卖图对象时最棒选用有含义的名字。
  2. 我们必要“posts”之间的关系,而这么些关系正是表Reply_to’。语法如下:
  1. 固然大家能选择其余别称,不过在拍卖图对象时最佳选拔有含义的名字。
  2. 咱俩须要“posts”之间的涉及,而这么些涉及正是表Reply_to’。语法如下:

联结表

假诺数量存款和储蓄在多个表中,如何用单条SELECT语句检索出多少?
应用联结。轻巧地说,联结是一种体制,用来在一条SELECT语句中关联表,因而称为联结。使用相当的语法,能够统1多少个表再次回到1组输出,联结在运营时关联表中准确的行。

–创立其它贰个表[SQLServer图数据库一些优点,使用子查询和联结表。SubTopic],在那之中有一字段是表[Topic]的主键,即是Foreign key
CREATE TABLE dbo.SubTopic
(
    SubTopicId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    TopicId INT,–外键 reference table dbo.Topic primary key
    Author NVARCHAR(50) NOT NULL,
    Content NVARCHAR(4000) NOT NULL    
)
GO

    

    

一、创建联结

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;

此语句的出口如下图所示:

997755.com澳门葡京 12

SELECT语句与前方的口舌同样钦赐要探求的列。这里,最大的出入是所钦定的五个列(prod_name和prod_price)在三个表中,而另一个列(vend_name)在另二个表中。那条语句的FROM子句列出了五个表,分别是vendors和products。它们正是那条SELECT语句联结的八个表的名字。那多个表用WHERE子句准确联结,WHERE子句提示MySQL相称vendors表中的vend_id和products表中的vend_id。

 

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

贰、内部统一(重要)

目前甘休所用的联结称为等值联结,它依据四个表之间的卓绝测试。那种统壹也称之为内部统壹。下边的SELECT语句重返与眼下例子完全一样的数量:

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;

此语句的出口如下图所示:

997755.com澳门葡京 13

此语句中的SELECT与前面包车型地铁SELECT语句同样,但FROM子句差别。那里,四个表之间的关联是FROM子句的组成都部队分,以
INNE奥迪Q5 JOIN
钦赐。在利用那种语法时,联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的平等。

接下去,便是触发器达成代码,当有论题被还原时,在主表中的Reply字段加1。

 

 

3、联结七个表

SQL对于一条SELECT语句中能够统1的表的数额没有限定。创造联结的主旨规则也壹如既往。首先列出富有表,然后定义表之间的涉嫌。比如:

SELECT prod_name,vend_name,prod_price,quantity FROM orderitems,vendors ,products WHERE products.vend_id=vendors.vend_id AND orderitems.prod_id=products.prod_id AND order_num=20005;

此语句的输出如下图所示:

997755.com澳门葡京 14

997755.com澳门葡京 15997755.com澳门葡京 16代码

  1. 在WHERE
    子句中,大家供给关联全体的表,用上面那种MATCH语句来贯彻关系:

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

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

  1. 在WHERE
    子句中,大家要求关联全体的表,用上边那种MATCH语句来贯彻关系:

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

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

–为表[SubTopic]始建贰个布署的触发器
CREATE TRIGGER dbo.tri_SubTopic_Insert ON dbo.SubTopic
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @key INT
    SELECT @key = [TopicId] FROM inserted
    –当有记录插入时,同时更新表[Topic]的[reply]字段,回复数量增进壹.
    UPDATE [dbo].[Topic] SET [Reply] = [Reply] + 1 WHERE [TopicId] = @key    
END

 

 

GO

  1. 这几个语法很风趣:“-”破折号表示边的$From_id字段意味着关系,然后“->”破折号和不止号用边的$To_id字段表示关系。
  2. 因为精通这些小名有reply,那多少个外号有replied
    post,大家得以创设三个询问字段列表:

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

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

  1. 本条语法很风趣:“-”破折号表示边的$From_id字段代表关系,然后“->”破折号和超越号用边的$To_id字段表示关系。
  2. 因为精晓那一个小名有reply,那三个小名有replied
    post,大家能够营造三个询问字段列表:

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

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

 

 

 

当有论题被删去时,在主表中的Reply字段也呼应减1。

  1. 在关系型模型中平等效果的询问如下:
  1. 在关系型模型中一样功能的查询如下:

997755.com澳门葡京 17997755.com澳门葡京 18代码

select RepliedPost.PostId,RepliedPost.PostTitle,

   ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle

  from Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost

  where ReplyPost.PostId=RepliedPost.ReplyTo
select RepliedPost.PostId,RepliedPost.PostTitle,

   ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle

  from Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost

  where ReplyPost.PostId=RepliedPost.ReplyTo

–为表[SubTopic]创办3个剔除触发器
CREATE TRIGGER dbo.tri_SubTopic_Delete ON dbo.SubTopic
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @key INT    
    SELECT @key = [TopicId] FROM deleted
    –在有记录删除时,同时更新表[Topic]的[reply]字段,回复数量减一.
    UPDATE [dbo].[Topic] SET [Reply] = [Reply] – 1 WHERE [TopicId] = @key    
END

 

 

GO

  1. 这一个查询很一般,当然MATCH的语法更易于通晓。
  2. 实施完上边语句询问结果如下:
  1. 那么些查询很一般,当然MATCH的语法更易于领悟。
  2. 执行完下边语句询问结果如下:

 

 997755.com澳门葡京 19

 997755.com澳门葡京 20

最终3个触发器,就是主表的论题被删除时,全数子表相应本论题的过来应也漫天去除,

  1. 我们抬高写那个回复贴人的名字。供给在FROM子句中增添‘ForumMembers’节点和‘Written_By’本条边。语句如下:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost, dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By

  1. 我们抬高写那个回复贴人的名字。供给在FROM子句中增添‘ForumMembers’节点和‘Written_By’其一边。语句如下:

    FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost, dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By

997755.com澳门葡京 21997755.com澳门葡京 22代码

 

 

CREATE TRIGGER dbo.tri_Topic_Delete ON dbo.Topic
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON    
    DECLARE @key INT    
    SELECT @key = [TopicId] FROM deleted
    DELETE FROM [dbo].[SubTopic] WHERE [TopicId] = @key
END

  1. 还要增多MATCH语句的在那之中关系:
  1. 还要加多MATCH语句的里边境海关系:

 

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

 

 

 

  1. 那就能够在SELECT列表中增多回帖人的名字,最后的询问如下:
  1. 这就可以在SELECT列表中加多回帖人的名字,最后的询问如下:
-- 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)
-- 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)

 

 

  1. 在关系型模型中的对应查询如下:

    SELECT RepliedPost.PostId,RepliedPost.PostTitle,

    ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle,

    RepliedMember.MemberName

    FROM Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost,

    Forum.ForumMembers RepliedMember

    WHERE ReplyPost.PostId=RepliedPost.ReplyTo

        and RepliedPost.OwnerId=RepliedMember.MemberId
    
  1. 在关系型模型中的对应查询如下:

    SELECT RepliedPost.PostId,RepliedPost.PostTitle,

    ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle,

    RepliedMember.MemberName

    FROM Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost,

    Forum.ForumMembers RepliedMember

    WHERE ReplyPost.PostId=RepliedPost.ReplyTo

        and RepliedPost.OwnerId=RepliedMember.MemberId
    

 

 

  1. 结果如下所示:
  1. 结果如下所示:

 997755.com澳门葡京 23

 997755.com澳门葡京 24

  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. 还贫乏回复对象的名字。像上边同样扩大‘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. 接下来,修改MATCH子句,‘ReplyMember’须求关联‘ReplyPost’,然则怎样去处理那些关系而不影响别的关联?供给用区别的法子来促成:

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

  1. 接下来,修改MATCH子句,‘ReplyMember’亟需关联‘ReplyPost’,不过如何去管理这些关系而不影响别的涉及?须要用分歧的措施来兑现:

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

 

 

  1. 小心这些标识“<-”与在此以前的反倒方向,不过意义是如出一辙的:2个在边表的$to_id与节点表的关联。
  2. 最终,还需追加写着过来的成员姓名,代码如下:

    — Posts and members and their replies and members

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

    ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle,

    ReplyMember.MemberName [ReplyMemberName]

      FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,
    
        dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By,
    
        dbo.ForumMembers ReplyMember, Written_By ReplyWritten_By
    
      WHERE MATCH(ReplyMember<-(ReplyWritten_By)-ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)
    
  1. 瞩目这么些符号“997755.com澳门葡京 ,<-”与事先的相反方向,不过意义是千篇一律的:叁个在边表的$to_id与节点表的涉嫌。
  2. 最后,还需追加写着过来的积极分子姓名,代码如下:

    — Posts and members and their replies and members

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

    ReplyPost.PostId as ReplyId, ReplyPost.PostTitle as ReplyTitle,

    ReplyMember.MemberName [ReplyMemberName]

      FROM dbo.ForumPosts ReplyPost, dbo.Reply_to, dbo.ForumPosts RepliedPost,
    
        dbo.ForumMembers RepliedMember, Written_By RepliedWritten_By,
    
        dbo.ForumMembers ReplyMember, Written_By ReplyWritten_By
    
      WHERE MATCH(ReplyMember<-(ReplyWritten_By)-ReplyPost-(Reply_to)->RepliedPost-(RepliedWritten_by)->RepliedMember)
    

 

 

  1. 结果集如下:
  1. 结果集如下:

 997755.com澳门葡京 25

 997755.com澳门葡京 26

  1. 在关系型查询的相应语句:

    SELECT RepliedPost.PostId,RepliedPost.PostTitle,

         RepliedMember.MemberName, ReplyPost.PostId as ReplyId,
    
      ReplyPost.PostTitle as ReplyTitle, ReplyMember.MemberName
    

    FROM Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost,

       Forum.ForumMembers RepliedMember, Forum.ForumMembers ReplyMember
    

    WHERE ReplyPost.PostId=RepliedPost.ReplyTo

        and RepliedPost.OwnerId=RepliedMember.MemberId
    
        and ReplyPost.OwnerId=ReplyMember.MemberId
    
  1. 在关系型查询的应和语句:

    SELECT RepliedPost.PostId,RepliedPost.PostTitle,

         RepliedMember.MemberName, ReplyPost.PostId as ReplyId,
    
      ReplyPost.PostTitle as ReplyTitle, ReplyMember.MemberName
    

    FROM Forum.ForumPosts ReplyPost, Forum.ForumPosts RepliedPost,

       Forum.ForumMembers RepliedMember, Forum.ForumMembers ReplyMember
    

    WHERE ReplyPost.PostId=RepliedPost.ReplyTo

        and RepliedPost.OwnerId=RepliedMember.MemberId
    
        and ReplyPost.OwnerId=ReplyMember.MemberId
    

 

 

  1. 在这一年,只怕在关系型形式里面随着关系的扩展读取就会越困难,而在图数据情势中MATCH子句相对就便于繁多。让我们看一下在图数据情势中部分风趣又实用的地点。
  1. 在那一年,大概在关系型形式里面随着关系的充实读取就会越困难,而在图数据情势中MATCH子句相对就轻便诸多。让我们看一下在图数据格局中有的有趣又使得的地点。

总计每篇帖子的回复数

总计每篇帖子的回复数

  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)
  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)

 

 

在那个讲话中大家总计了每一篇回复的多寡,不过一味在2个范围中,并不是在全体还原的树结构里面。

在这一个讲话中我们总结了每1篇回复的多寡,不过单纯在二个范畴中,并不是在任何还原的树结构里面。

根贴(主贴)的列表

根贴(主贴)的列表

咱俩因而上面不接纳MATCH的话语得到全部的根贴:

咱俩因而上面不采用MATCH的言辞获得全部的根贴:

SELECT Post1.PostId,Post1.PostTitle

  FROM dbo.ForumPosts Post1

  WHERE $node_id not in (select $from_id from dbo.Reply_To
SELECT Post1.PostId,Post1.PostTitle

  FROM dbo.ForumPosts Post1

  WHERE $node_id not in (select $from_id from dbo.Reply_To

 

 

MATCH语法只是允许大家提到四个恐怕越多的实业(比如八个节点和二个涉及)。当大家只想关联当中五个的时候,只要求3个正规的接二连三只怕子查询。如上边的说话同样。

MATCH语法只是允许大家关系多少个也许越多的实业(举个例子八个节点和一个关乎)。当大家只想关联个中三个的时候,只供给三个好端端的连年大概子查询。如上面包车型客车语句同样。

在结果中增加‘Level’字段

在结果中增添‘Level’字段

丰硕三个‘Level’字段,展现树结构。在T-SQL中有二个差不离的语法,叫做CTE落成递归。然则有2个主题材料,不能够动用MATCH语法在一个派生表上,此时得以应用CTE。倘若有须求,能够在CTE中利用MATCH,不过反之就格外了,有这么的限量。上边呈现一下接纳正规的涉及仅仅使用CTE来迭代,代码如下:

增加2个‘Level’字段,显示树结构。在T-SQL中有三个简约的语法,叫做CTE落成递归。可是有三个主题素材,不可能选择MATCH语法在一个派生表上,此时可以使用CTE。假若有要求,能够在CTE中应用MATCH,不过反之就丰硕了,有这么的限量。上面体现一下施用正规的涉嫌仅仅使用CTE来迭代,代码如下:

 with root as

  ( select $node_id as node_id,RootPosts.PostId,

           RootPosts.PostTitle,

           1 as Level, 0 as ReplyTo

     from dbo.ForumPosts RootPosts

     where $node_id not in (select $from_id from dbo.reply_to)

  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
 with root as

  ( select $node_id as node_id,RootPosts.PostId,

           RootPosts.PostTitle,

           1 as Level, 0 as ReplyTo

     from dbo.ForumPosts RootPosts

     where $node_id not in (select $from_id from dbo.reply_to)

  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

 

 

 

 

探索2个帖子中的全数回复

搜寻2个帖子中的全部回复

动用CTE递归语法,我们得以用一种树结构检索一个帖子的持有回复。假若利用正规的语法不可能在搜索帖子一的时等候检查索贴子三,因为三是对二的复原,而2是对一的复原。使用CTE.当查询帖子一的兼具回复时能检索贴子三。代码如下:

应用CTE递归语法,我们能够用一种树结构检索二个帖子的持有回复。借使应用正规的语法不可能在研究帖子一的时等候检查索贴子三,因为三是对二的过来,而贰是对一的死灰复燃。使用CTE.当查询帖子1的装有回复时能检索贴子叁。代码如下:

 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
 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

 

 

咱俩也得以扭转做,在树状结构中按顺序检索全部父贴。由于CTE不帮助OUTERAV四join,所以要在表面增添,代码如下:

作者们也得以扭转做,在树状结构中按梯次检索全体父贴。由于CTE不帮助OUTE揽胜join,所以要在表面增多,代码如下:

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
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

搜索3个用户全部帖子

寻找二个用户全部帖子

查询三个用具有的新闻,与帖子差别,那不须要树,要简明不少:

查询二个用具备的音讯,与帖子不一样,那不供给树,要简明不少:

--  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'
--  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'

 

 

想必你放在心上到地点多个查询的两样,便是在显示字段上是或不是选取DISTINCT。那几个去重是因为Peter回复同2个帖子能够当先一次。

或然你注意到下边七个查询的不等,正是在体现字段上是或不是利用DISTINCT。这一个去重是因为Peter回复同3个帖子能够超越贰回。

在模型中检索Likes(点赞)

在模型中检索Likes(点赞)

以此查询是有趣的:‘Likes’边是成员和发帖表的涉及。每1个关乎都以唯一的,并不受别的关联影响。代码如下:

其一查询是风趣的:‘Likes’边是成员和发帖表的关系。每七个关系都以绝无仅有的,并不受别的涉嫌影响。代码如下:

--点赞的帖子或者被别人点赞的帖子。

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

  FROM   dbo.ForumPosts Post, Likes,

         dbo.ForumMembers Member

  WHERE MATCH(Member-(Likes)->Post)

  -- 点赞的人或者被人点赞

  SELECT Member.MemberId,Member.MemberName LikeMember,

         LikedMember.MemberName LikedMemberName

  FROM dbo.ForumMembers Member, Likes, dbo.ForumMembers LikedMember

  WHERE MATCH(Member-(Likes)->LikedMember)
--点赞的帖子或者被别人点赞的帖子。

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

  FROM   dbo.ForumPosts Post, Likes,

         dbo.ForumMembers Member

  WHERE MATCH(Member-(Likes)->Post)

  -- 点赞的人或者被人点赞

  SELECT Member.MemberId,Member.MemberName LikeMember,

         LikedMember.MemberName LikedMemberName

  FROM dbo.ForumMembers Member, Likes, dbo.ForumMembers LikedMember

  WHERE MATCH(Member-(Likes)->LikedMember)

 

 

还足以很轻松地集结消息,以取得每种帖子或种种成员的总的Likes。

还足以很轻易地围拢音信,以获取每种帖子或各类成员的总的Likes。

--每个帖子总的likes

  select Post.PostId,Post.PostTitle,

         count(*) totalLikes

  from dbo.ForumPosts Post,Likes,

       dbo.ForumMembers Members

  where Match(Members-(Likes)->Post)

  group by PostId,PostTitle

  --每个成员总的点赞数select LikedMembers.MemberId,LikedMembers.MemberName,

         count(*) totalLikes

  from dbo.ForumMembers Members,Likes,

       dbo.ForumMembers LikedMembers

  where Match(Members-(Likes)->LikedMembers)

  group by LikedMembers.MemberId,

           LikedMembers.MemberName
--每个帖子总的likes

  select Post.PostId,Post.PostTitle,

         count(*) totalLikes

  from dbo.ForumPosts Post,Likes,

       dbo.ForumMembers Members

  where Match(Members-(Likes)->Post)

  group by PostId,PostTitle

  --每个成员总的点赞数select LikedMembers.MemberId,LikedMembers.MemberName,

         count(*) totalLikes

  from dbo.ForumMembers Members,Likes,

       dbo.ForumMembers LikedMembers

  where Match(Members-(Likes)->LikedMembers)

  group by LikedMembers.MemberId,

           LikedMembers.MemberName

 

 

用户点赞并且恢复生机帖子

用户点赞并且复苏帖子

咱俩也足以创制一些更加风趣的查询,举例,查找那么些点赞并回复的人,如下:

咱俩也得以创制一些更有意思的查询,比方,查找那几个点赞并回复的人,如下:

  SELECT Member.MemberName,Member.Memberid,

         LikedPost.PostId,LikedPost.PostTitle,

         ReplyPost.PostTitle ReplyTitle

  FROM dbo.ForumPosts LikedPost, Reply_To, dbo.ForumPosts ReplyPost,

       Likes, dbo.ForumMembers Member, Written_By

  WHERE MATCH(Member-(Likes)->LikedPost<-(Reply_To)-ReplyPost-(Written_By)->Member)
  SELECT Member.MemberName,Member.Memberid,

         LikedPost.PostId,LikedPost.PostTitle,

         ReplyPost.PostTitle ReplyTitle

  FROM dbo.ForumPosts LikedPost, Reply_To, dbo.ForumPosts ReplyPost,

       Likes, dbo.ForumMembers Member, Written_By

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

 

 

注意,对于‘Member’节点使用了一遍在同2个MATCH表达式中。那产生了一种过滤:点赞并且有东山再起的分子,必要在‘LikedPost’和‘ReplyPost’中都有记录才得以。

注意,对于‘Member’节点使用了五回在同一个MATCH表达式中。那产生了1种过滤:点赞并且有还原的成员,供给在‘LikedPost’和‘ReplyPost’中都有记录才得以。

那么在关系型格局中代码如下:

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

select Likes.MemberId,Members.MemberName

  from Forum.Likes Likes, Forum.ForumPosts Posts,

       Forum.ForumMembers Members

  where Likes.MemberId=Posts.OwnerId

   and Posts.ReplyTo=Likes.PostId

   and Members.MemberId=Likes.MemberId
select Likes.MemberId,Members.MemberName

  from Forum.Likes Likes, Forum.ForumPosts Posts,

       Forum.ForumMembers Members

  where Likes.MemberId=Posts.OwnerId

   and Posts.ReplyTo=Likes.PostId

   and Members.MemberId=Likes.MemberId

 

 

看起来那种写法更难了解和读懂。

看起来那种写法更难明白和读懂。

回帖给五个帖子的积极分子

回帖给三个帖子的积极分子

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
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

 

 

回帖个二个帖子多次的分子:

回帖个三个帖子多次的成员:

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
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

 

 

上述三种语句中唯一的差异就是展现结果的联谊。

上述两种语句中绝无仅有的不等正是显得结果的集纳。

总结

  因而上述营造在图数据情势下的查询和涉及,相比较了常规语句以及在事关格局下的1致查询,轻松窥见无论是在易读性,逻辑明白上或许在性质上都有极大增加。当然那只是率先个本子,所以难免有众多难题,
下一篇笔者讲介绍这一个本子存在的壹有个别标题。

总结

  通过上述创设在图数据方式下的询问和涉嫌,比较了常规语句以及在涉及方式下的等同查询,轻松开掘无论是在易读性,逻辑驾驭上或然在性质上都有相当的大加强。当然那只是首先个本子,所以难免有众多标题,
下一篇作者讲介绍那么些本子存在的一片段难题。

相关文章

发表评论

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

*
*
Website