当前位置: 澳门新濠3559 > 数据库 > 正文

逻辑表达式的最小组合是,Server从很早的版本就

时间:2019-11-07 13:43来源:数据库
在数据库开发中,对两个关系表进行连接查询,能够直接做“逻辑或”的查询,而对于逻辑与和逻辑非的查询,则稍复杂点,需要编写额外的代码来实现。在关系型数据库中,所谓的连

在数据库开发中,对两个关系表进行连接查询,能够直接做“逻辑或”的查询,而对于逻辑与和逻辑非的查询,则稍复杂点,需要编写额外的代码来实现。在关系型数据库中,所谓的连接,实际上是集合的包含,只要包含一项,就满足连接条件,实现的逻辑或,这种设计,能够满足绝大多数的查询需求。有时,对于一条数据,可能需要通过多个逻辑表达式来定性,比如,判定一篇文章是否跟Microsoft Azure有关,通常简单的做法是从多个关键字的逻辑组合来定性:文章中同时含有关键字“Microsoft”和“Azure”,或者同时含有关键字“Windows”和“Azure”,把这种逻辑组合抽象成表达式,就是:( Microsoft & Azure ) | ( Windows & Azure )。

在传递数据时,XML和JSON是最常用的数据格式,SQL Server从很早的版本就开始支持XML格式,而对于JSON格式,SQL Server从2016版本开始支持。大多数数据库系统并没有升级到SQL Server 2016版本,因此在传递格式化的数据时,通常还是使用XML格式。对我而言,查询和解析XML格式的数据需要掌握的知识点较多,MSDN上关于XML的文档,又试图把XML的各个方面都讲解地清清楚楚,以至于内容冗杂,使学习过程变得困难。我十分不喜欢学习这些不常用的数据结构,再说,在平时的数据库开发中,用到XML的地方也很少,可是,一旦在应用程序中用到XML,就只有头疼的份了,既然避不开XML,那就用最简单的方法学习它,了解它,使用它,以备不时之需。写这篇文章,就是以最简单的方式,分享XML最常用的使用方法。

逻辑表达式的基本操作符是:与(&),或(|)和非(!),逻辑表达式的最小组合是:A&B,A|B 和 !A。关系型数据库的开发人员,在设计逻辑表达式时,必须保证满足业务需求,同时,尽可能支持多种逻辑组合,通常情况下,按照表达式的关系,我们把逻辑表达式拆分成四个元数据类型:Expression(表达式),SubExpression(子表达式),Operator(操作符)和Operand(操作数)。

一,XML数据格式的简单介绍

在我目前接触的项目中,业务需求的逻辑表达式的组合相对简单,标准的表达式格式如下所示:

1,最简单的XML格式

Expression = ((A & B) | (C & D)) & !(E | F)

XML数据最简单的格式是:

该逻辑表达式表示:查询语句返回的结果集中,不能包含E和F,但是,必须包含A和B,或者包含C且不包含D。

  • 开始标签:<tag>
  • 标签的属性,属性值用双引号:<tag id="1" name="azure">,在单个节点中,属性名不能重复,属性之间使用空格分隔,在开始标签中,才能设置属性;
  • 结束标签:</tag>,结束标签不能有属性;
  • 子节点:在开始标签和结束标签,可以包含节点,叫做子节点;
  • 节点值:在开始标签和结束标签的标量值,叫做节点值;

对于该类表达式,我们可以抽象成更为通用的逻辑公式是:

2,使用字符串对XML数据赋值

Expression= (SubExpression1 | SubExpression2 | ...) & !ExcludeExpression
SubExpression=Operand1 & Operand2 & ...
ExcludeExpression=Operand1 | Operand1 | ...

数据类型XML用于存储XML格式化的文本数据,在本例中,声明一个XML类型的变量 @xml,并赋值,后文示例都使用该变量用于数据查询。

这种高度概括的表达式蕴含的逻辑关系是:SubExpression中操作数之间的关系是逻辑与,ExcludeExpression中操作数之间的关系是逻辑或,该表达式蕴含的意思是:任意一条数据,不能够包括ExcludeExpression中的任何一个项目,但是,必须至少满足一个子表达式(SubExpression),子表达式中的项目都是逻辑与的关系。

declare @xml xml
set @xml='
<Expression ID="1" TaxonomyID="1">
  <SubExpression ID="1" OperandType="Tag" Operator="and">
    <Oprand ID="268819" Name="abuse" />
    <Oprand ID="277029" Name="mongohq" />
    <Oprand ID="516813" Name="access" />
  </SubExpression>
  <SubExpression ID="2" OperandType="Tag" Operator="and">
    <Oprand ID="283839" Name="reviews" />
    <Oprand ID="697348" Name="retention" />
  </SubExpression>
  <SubExpression ID="3" OperandType="Tag" Operator="not">
    <Oprand ID="281556" Name="richfaces" />
    <Oprand ID="2993766" Name="rgp" />
  </SubExpression>
</Expression>'

一,用关系表存储逻辑表达式

二,XPath路径表达式

举个例子,为了描述方便,我们用下面的逻辑表达式来说明:

XPath 使用路径表达式在 XML 文档中选取节点,节点是通过沿着路径选取的,XPath是查询XML数据时必备的参数。

Expression= ((A & B & C) | (C & D)) & !(E | F)

常用的路径表达式是:

该表达式可以拆分成三个子表达式:A & B & C,C & D, !(E | F),对于子表达式 (A & B & C,C & D),其操作数之间的关系都是逻辑与;而对于子表达式!(E | F),其操作数之间的关系是逻辑或。

  • .   :选取当前节点;
  • ..  :选取当前节点的父节点;
  • /   :从根节点开始;
  • //逻辑表达式的最小组合是,Server从很早的版本就开始支持XML格式。  :从匹配选择的节点开始选取,而不考虑其位置;
  • *  :通配符,匹配任意字符,或任意节点;
  • node()  :匹配任意节点,跟通配符 * 功能相似; 
  • @PropertyName :选取属性;

1,通过XML文档表示逻辑表达式

在路径表达式中,跟节点的选取有关的表达式是:

逻辑表达式的子表达式之间,不是无关系的,为了在不同的应用程序间传递逻辑表达式,数据结构必须包含该表达式的所有关系和操作数,XML文档特别适合表达有特定关系的数据结构,按照之前拆分的四种元数据类型,我们把XML文档定义为三种不同的标签,分别是<Expression>,<SubExpression>和<Operand>,并为标签设置不同的属性,通过格式化的XML实现逻辑表达式的转存,示例如下:

  • NodeName:选取指定节点名及其所有子节点;
  • NodeName[N]:选取指定节点集合的第N个节点;
  • NodeName[@Name]:选取当前节点中带有指定属性的节点;
declare @xml xml
set @xml='
<Expression ID="1">
  <SubExpression ID="1" OperandType="Tag" Operator="and">
    <Operand ID="1" />
    <Operand ID="2" />
    <Operand ID="3" />
  </SubExpression>
  <SubExpression ID="2" OperandType="Tag" Operator="and">
    <Operand ID="4" />
    <Operand ID="5" />
  </SubExpression>
  <SubExpression ID="3" OperandType="Tag" Operator="not">
    <Operand ID="6"  />
    <Operand ID="7"  />
  </SubExpression>
</Expression>'

三,XML数据的查询(query()函数

2,把逻辑表达式存储到关系表

@xml.query(’xpath‘)函数,参数是路径表达式,返回XML数据类型的结果,该XML是非类型化(untyped)的。

XML格式的文档适合数据的传递,不适合直接存储在关系型数据库中,并且XML格式的文档也不利于对数据执行查询操作,因此,必须把XML文档存储到数据表中,以利用关系数据库引擎执行集合操作的优势,实现数据的快速查询。当应用程序接收到这个XML文档,必须把XML蕴含的表达式拆分成:表达式(Expression),子表达式(SubExpression),操作数(Operand)和操作符(Operator),例如,我们可以利用以下脚本创建数据表:

1,选取节点及其子节点

create table dbo.Operands
(
    ExpressionID bigint not null,    
    SubExpressionID smallint not null,
    OperandID bigint not null,          --EntityID, TagID
    Operator varchar(8) not null,       --&,!
    OperandType varchar(8) not null,    --Entity,Tag
    primary key clustered(ExpressionID,SubExpressionID,OperandID)
)
with(data_compression=page);
go

示例中,从根节点Expression开始,选取SubExpression节点及其子节点:

注:OperandType是操作数的类型,分为:Entity和Tag,相应的操作数ID(OperandID)就是EntityID和TagID,本例只使用TagID,EntityID没有使用。

select @xml.query('/Expression/SubExpression')

SQL Server内置函数用于解析XML文档,通过XML解析函数,我们可以利用TSQL脚本把XML文档插入到表Operands中。这意味着,在表Operands中,当Operator列值为not时,表示逻辑非,表示OperandID是<ExcludeExpression>标签存储的操作数;当Operator列值为and时,表示逻辑与,表示OperandID是<SubExpression>标签存储的操作数。

query()函数返回的结果如下,该查询结果是非类型化的XML数据。

图片 1图片 2

<SubExpression ID="1" OperandType="Tag" Operator="and">
  <Oprand ID="268819" Name="abuse" />
  <Oprand ID="277029" Name="mongohq" />
  <Oprand ID="516813" Name="access" />
</SubExpression>
<SubExpression ID="2" OperandType="Tag" Operator="and">
  <Oprand ID="283839" Name="reviews" />
  <Oprand ID="697348" Name="retention" />
</SubExpression>
<SubExpression ID="3" OperandType="Tag" Operator="not">
  <Oprand ID="281556" Name="richfaces" />
  <Oprand ID="2993766" Name="rgp" />
</SubExpression>
;with cte_Expressions as 
(
    select e.v.query('.') as Expression
        ,e.v.value('@ID','int') as ExpressionID
    from @xml.nodes('/Expression') as e(v)
)
,cte_SubExpression as 
(
    select e.ExpressionID
        ,se.SubExpression
        ,se.SubExpressionID
        ,se.OperandType
        ,se.Operator
    from cte_Expressions e
    cross apply
    (
        select t.v.query('.') as SubExpression
            ,t.v.value('@ID','int') as SubExpressionID
            ,t.v.value('@OperandType','varchar(16)') as OperandType
            ,t.v.value('@Operator','varchar(16)') as Operator
        from e.Expression.nodes('/Expression/SubExpression') as t(v)
    ) as se
)
--insert into dbo.Operands
select p.ExpressionID
    ,p.SubExpressionID
    ,d.OperandID
    ,p.Operator
    ,p.OperandType
from cte_SubExpression p
cross apply
(
    select t.v.value('@ID','int') as OperandID
        ,t.v.value('@Exclude','int') as Exclude
    from p.SubExpression.nodes('/SubExpression/Operand') as t(v)
) as d

2,选取指定节点的所有子节点集合

View Code

select @xml.query('/Expression/SubExpression/node()')
select @xml.query('/Expression/SubExpression/*')

二,使用TSQL实现逻辑与和逻辑非

结果集是SubExpression节点下的所有子节点: 

数据表包含的数据是Tag和Data之间的映射关系,如果Data包含的Tag满足逻辑表达式,那么就认为Data和逻辑表达式(Expression)之间存在映射关系,也就是说,Data满足Expression。

<Oprand ID="268819" Name="abuse" />
<Oprand ID="277029" Name="mongohq" />
<Oprand ID="516813" Name="access" />
<Oprand ID="283839" Name="reviews" />
<Oprand ID="697348" Name="retention" />
<Oprand ID="281556" Name="richfaces" />
<Oprand ID="2993766" Name="rgp" />

例如,有以下数据表DataTable,该表只有两列DataID和TagID,表示Data具有相应的Tag:

四,XML数据的查询(value()函数)

图片 3图片 4

@xml.value('xpath','sql_data_type'),返回XML数据中单个属性的标量值,在使用value()函数时,xpath 参数必须指定返回的是单个值,而value()函数不会去check返回值的数量。

create table dbo.DataTable 
(
    DataID bigint not null,
    TagID bigint not null,
)
go

insert into dbo.DataTable
(DataID,TagID)
values
(1,1)
,(1,2)
,(1,3)
,(1,7)
,(2,4)
,(2,5)
,(3,1)
,(3,2)
,(3,3)
,(4,1)
,(4,3)
,(6,6)
go

一般情况下,即使xml数据只有一个属性值,静态类型化(Static typing)要求,xpath表达式也必须显式指定返回单个标量值,因此,必须指定在xpath函数的末尾添加”[1]“,通常的xpath表达式是”(xpath)[1]“。

View Code

select @xml.value('(/Expression/SubExpression[1]/@ID)[1]','int')
select @xml.value('(/Expression/SubExpression/@ID)[1]','int')

1,逻辑非的实现

五,XML数据的查询(nodes()函数) 

逻辑非的含义是:在DataTable中,一个DataID不能包含任意一个ExcludeExpression中的TagID。

@xml.nodes ('xpath') 函数返回节点的集合,用于把XML数据转换为关系数据表,返回的每一个行都是XML数据类型,语法是:

;with cte_exclude_data
as
(
    select distinct 
        dt.DataID
    from dbo.DataTable dt
    inner join dbo.Operands o
        on dt.TagID=o.OperandID
    where Operator='not'
)
select dt.DataID
    ,dt.TagID
from dbo.DataTable dt
left join cte_exclude_data ed
    on dt.DataID=ed.DataID
where ed.DataID is null
nodes ('xpath') as table(column)  

2,逻辑与的实现

通过nodes()函数,返回SubExpression节点及其属性,由于单个节点中,属性名不可能重复,因此,在nodes()函数返回的单个节点中,不需要通过xpath路由,直接获取当前节点的属性值,这样,可以在xpath表达式中直接指定属性,不需要显式以“[1]”结尾。

 逻辑与的含义是:在DataTable中,一个DataID必须包含SubExpression中的所有Tag。

示例代码如下,在value()函数中,直接指定属性值,表示获取当前节点的属性值:

;with cte_operands as 
(
    select o.ExpressionID
        ,o.SubExpressionID
        ,o.OperandID
        ,count(0) over(partition by o.ExpressionID,o.SubExpressionID) as Operands
    from dbo.Operands o
    where o.Operator='and'
)
select o.ExpressionID
    ,o.SubExpressionID
    ,o.Operands
    ,dt.DataID
from cte_operands o
inner join dbo.DataTable dt
    on o.OperandID=dt.TagID
group by o.ExpressionID
    ,o.SubExpressionID
    ,o.Operands
    ,dt.DataID
having count(distinct dt.TagID)=o.Operands
select t.v.query('.') as SubExpression
    ,t.v.value('@ID','int') as SubExpressionID
    ,t.v.value('@OperandType','varchar(16)') as OperandType
    ,t.v.value('@Operator','varchar(16)') as Operator
from @xml.nodes('/Expression/SubExpression') as t(v)

3,查询脚本示例 

图片 5

把逻辑与和逻辑非的代码合并到一起,就能实现表达式蕴含的逻辑,这里给出一个示例脚本:

通过cross apply 连接操作,把SubExpression节点下的所有数据都转换为关系型数据,并把该数据存储到临时数据表#Expressions中:

图片 6图片 7

图片 8图片 9

;with cte_operands as 
(
    select o.ExpressionID 
        ,o.SubExpressionID
        ,o.OperandID
        ,count(o.OperandID) over(partition by o.ExpressionID,o.SubExpressionID) as Operands
    from dbo.Operands o
    where Operator='and'
)
,cte_exclude_data as 
(
    select distinct 
        d.DataID
    from dbo.DataTable d
    inner join dbo.Operands o
        on d.TagID=o.OperandID 
    where o.Operator='not'
)
,cte_data as 
(
    select d.DataID
        ,d.TagID
    from dbo.DataTable d
    left join cte_exclude_data ed
        on d.DataID=ed.DataID
    where ed.DataID is null
)
select o.ExpressionID
    ,o.SubExpressionID
    ,dt.DataID
from cte_operands o
inner join cte_data dt
    on o.OperandID=dt.TagID
group by o.ExpressionID
    ,o.SubExpressionID
    ,dt.DataID
    ,o.Operands
having count(distinct dt.TagID)=o.Operands
;with cte_Expressions as 
(
    select e.v.query('.') as Expression
        ,e.v.value('@ID','int') as ExpressionID
        ,e.v.value('@TaxonomyID','int') as TaxonomyID
    from @xml.nodes('/Expression') as e(v)
)
,cte_SubExpression as 
(
    select e.ExpressionID
        ,e.TaxonomyID
        ,se.SubExpression
        ,se.SubExpressionID
        ,se.OperandType
        ,se.Operator
    from cte_Expressions e
    cross apply
    (
        select t.v.query('.') as SubExpression
            ,t.v.value('@ID','int') as SubExpressionID
            ,t.v.value('@OperandType','varchar(16)') as OperandType
            ,t.v.value('@Operator','varchar(16)') as Operator
        from e.Expression.nodes('/Expression/SubExpression') as t(v)
    ) as se
)
select p.TaxonomyID
    ,p.ExpressionID
    ,p.SubExpressionID
    ,p.OperandType
    ,p.Operator
    ,d.OperandID
    ,d.OperandName
from cte_SubExpression p
cross apply
(
    select t.v.value('@ID','int') as OperandID
        ,t.v.value('@Name','varchar(32)') as OperandName
    from p.SubExpression.nodes('/SubExpression/Oprand') as t(v)
) as d

View Code

View Code

在我参与的项目中,这种设计(Design)模式已经用于产品环境,能够满足业务查询的需求,但是查询速度不是很理想,有待优化。

图片 10

 

六,把行集数据转化为XML数据(for xml path)

附上较复杂的逻辑表达式,该格式能够表达的逻辑组合更为丰富,后续我会分享深入探索的随笔:

把行集数据转化为XML数据,需要用到for xml path子句,该子句的特点是:

Expression = ((A & B) | (C & ! D)) & !(E | F)

path('root') 子句用于指定根节点;

本文列举的逻辑表达式非常简单,鉴于本人的知识和经验有限,难免有纰漏,如果有更好的解决方案,还请不吝告知,十分感谢!

select子句的字段别名用于指定属性,别名中必须使用@符号标识出属性名,例如:'@PropertyName';

 

在select 子句中,如果不在别名中把字段指定为属性,那么该字段的值作为节点值,节点值分为标量类型和XML类型;

  • 对于标量类型,节点值是标量值;
  • 对于XML类型,节点值是子节点的集合;

例如,要把数据转换为如下的关系型数据结构,其SubExpression字段是一个非类型化的XML数据,要完成这样的数据转换,必须使用for xml path子句和cast()类型转换:

图片 11

<SubExpression ID="1" OperandType="Tag" Operator="not">
  <Oprand ID="268819" Name="abuse" />
  <Oprand ID="277029" Name="mongohq" />
  <Oprand ID="516813" Name="access" />
</SubExpression>
<SubExpression ID="2" OperandType="Tag" Operator="not">
  <Oprand ID="283839" Name="reviews" />
  <Oprand ID="697348" Name="retention" />
</SubExpression>
<SubExpression ID="3" OperandType="Tag" Operator="not">
  <Oprand ID="281556" Name="richfaces" />
  <Oprand ID="2993766" Name="rgp" />
</SubExpression>

使用类型转换的目的,是为了把for xml path返回的字符串转换成XML数据类型,这样,就能以XML格式嵌入到上次的for xml path的结构中,作为子节点:

图片 12图片 13

;with cte_Expressions as 
(
    select distinct ExpressionID
        ,TaxonomyID
    from #Expressions with(nolock)
) 
,cte_SubExpressions as
(
    select o.ExpressionID
        ,o.SubExpressionID
        ,o.OperandType
        ,o.Operator
    from #Expressions o with(nolock)
    group by o.ExpressionID
        ,o.SubExpressionID
        ,operandType
        ,o.Operator
)
select e.TaxonomyID as TaxonomyID
    ,e.ExpressionID as ExpressionID
    ,cast(
    (
        select o.SubExpressionID as '@ID'
            ,o.OperandType as '@OperandType'
            ,case o.Operator when '&' then 'and' else 'not' end as '@Operator'
            ,cast((
                select op.OperandID as '@ID'
                    ,op.OperandName as '@Name'
                from #Expressions op with(nolock)
                where o.ExpressionID=op.ExpressionID
                    and o.SubExpressionID=op.SubExpressionID
                for xml path('Oprand')            
            )as xml)
        from cte_SubExpressions o
        where o.ExpressionID=e.ExpressionID
        for xml path('SubExpression')
        ) as xml)
        as SubExpressions
from cte_Expressions e

View Code

还有两个函数modify()和exist(),用于XML数据的修改和检查,由于在我当前接触的项目中,没有用到过,我就不写了。

到此,文章也该结尾了,XML的极简用法已经总结了很多,在以后工作中国,如果用到XML时,翻开这篇文章,能够快速解决XML常见的数据查询和解析问题,这样就足够了。

 

参考文档:

Use PATH Mode with FOR XML.aspx)

XML Data (SQL Server)

编辑:数据库 本文来源:逻辑表达式的最小组合是,Server从很早的版本就

关键词: