第一篇.Sql Server逻辑查询处理

       闲言碎语不要讲，今天来表一表山东好汉武二郎，嗯。。。跑题了！先说一说为什么要写这些东西，Sql编程有许多独特的方面，比如：面向集合的编程思想、查询的逻辑处理次序、三值逻辑等。如果不掌握这些只是就开始使用Sql，得到的也是一些性能低下难以维护的代码。今天主要写的是查询处理的逻辑方面，在阅读文章的时候，先不要考虑性能问题，主要是理解查询逻辑的次序，后面我还会单独写一些关于性能方面的内容。

   (8)  select (9) distinct(11)   <top>  <select_list>  

   (1)    from   <left_table>   

   (3)  <join_type>  join <right_table> 

   (2)  on <join_condition>

   (4)    where <where_condition>

   (5)  group by <groupby_list>

   (6)    with {cube|rollup}

   (7)  having <having_condition>

   (10)   order by <orderby_list>

     以上是查询逻辑的步骤序号，每一个步骤都会产生一个虚拟表，作为下一个步骤的输入，这些虚拟表对于调用者是不可用的，只有当最后一个步骤完成以后才会返回给调用者，如果在查询中没有相应的字句，则跳过相应的步骤。下面我们来简要说一下每个步骤：

     1. from:对from字句的前两个表执行笛卡尔积（交叉连接），生成虚拟表VT1

     2.on:对VT1应用on筛选器，只有那些使j<oin_condition>为真的行才被插入VT2

     3.outer(join):如果指定了outer join,没有匹配的行作为外部行添加到VT2生成VT3

     4.where :对VT3执行where筛选器，满足<where_condition>的行被插入VT4

     5.group by:对VT4进行行分组，生成VT5

     6.cube|rollup:把超组插入VT5生成VT6

     7.having:对VT6之心having 筛选器，只有满足<having_condition>的行被插入VT7

     8.select:处理select列表，生成VT8

     9. distinct:去除VT8表中的重复数据，生成VT9

     10.order by:将VT9按照<orderby_list>排序，生成一个游标VC10

     11.top:从游标VC10开始处选取指定的数量，生成VT11,返回给调用者

     接下来我们通过一个示例来介绍一下逻辑处理的每个阶段，创建消费者Customer和订单Orders表并填充数据：

       create table Customers
      (
      customerid nvarchar(5) primary key,
      city nvarchar(10) not null
      )

      create table Orders
      (
      orderid bigint identity(1,1) primary key,
      customerid nvarchar(5) not null
      )
      insert into Customers(customerid,city) values('FISSA','Madrid')
      insert into Customers(customerid,city) values('FRNDO','Madrid')
　　insert into Customers(customerid,city) values('KRLOS','Madrid')
　　insert into Customers(customerid,city) values('MRPHS','Zion')

　　insert into Orders(orderid,customerid) values(1,'FRNDO')
　　insert into Orders(orderid,customerid) values(2,'FRNDO')
　　insert into Orders(orderid,customerid) values(3,'KRLOS')
　　insert into Orders(orderid,customerid) values(4,'KRLOS')
　　insert into Orders(orderid,customerid) values(5,'KRLOS')
　　insert into Orders(orderid,customerid) values(6,'MRPHS')
　　insert into Orders(orderid,customerid) values(7,null)

    查询来自Madrid并且订单小于3的消费者，并且包含订单数，按照订单数从小到大排序，试着自己写一下这个需求，并按照上面的步骤分析一下自己写出来的语句，不想写也没关系，我们主要的目的是理解逻辑处理的步骤，下面给出这个需求的答案并分析每个步骤：

    select c.customerid,count(o.orderid) as numorders from Customer as c left join Orders as o on c.customerid=o.customerid where c.city='Madrid'  group by c.customerid having count(o.orderid)<3 order by numorders

 

　步骤1：执行笛卡尔乘机（交叉联接 ）

 　　　　对from 子句后面的前两个表执行笛卡尔乘机，生成虚拟表VT1。VT1左表的行和右表的行每一个可能的组合都包含一行，比如左表n行，右表m行，则VT1有n*m行。在该示例中 from Customer as c ... join Orders as o     Customer表中有4行，Orders表中有7行，则生成虚拟表VT1包含28行数据。

   步骤2：应用on筛选器（联接条件）

    　　   on筛选器是查询筛选器（on、where、having）中的一个，on筛选器应用VT1中所有的行，只有筛选条件为true的行被插入到虚拟表VT2中，在该示例中on c.customerid=o.customerid 。这里讲到筛选条件了，顺便说一下Sql的一个重要特性，在Sql逻辑表达式的可能值包括True、False、Unknown，被称为三值逻辑。在大多数的编程语言中的逻辑表达式只有True或False两种值。Sql中的Unknown值主要出现在包含null的逻辑表达式中，在Sql中null通常表示丢失或者不相关，当比较丢失值和另外一个值得时候（null>40，null=null ,x+null>y），结果总是Unknown。处理unknown和null的时候总是容易混淆，not true等于false,not false 等于true,not unknown等于unknown。

 　　　　unknown逻辑结果和null在不同的语言元素中被区别对待，在查询筛选器(on、where、having)中,把unknown当作false来处理，使筛选器的结果为unknown的行都会被排除在结果集之外。而在check约束中，unknown被当作true来处理。在筛选器中比较两个null值将得到unknown,被当作false处理。而unique约束、group by 分组操作、排序操作认为两个null值是相等的（如果一个表中为某一列定义了unique约束，则无法向表中插入该列值为null的两行、group by 会把所有null值分到一组、order by 会把所有null值排到一起）。

   步骤3：添加外部行

　　　　这一步只和外部联接（left join、right join、full join）有关，把其中的一个表标记为保留表，另外一个表标记为外部表，保留表返回所有的行。左外部联接（left join ）是将左表当作保留表、右外部联接（right join）是将右表当作保留表、完全外部联接将左表和又表都当作保留表。步骤3返回VT2中的所有行以及在步骤2中被过滤掉的保留表中的行，保留表中的这些行被称为外部行，外部行中非保留表的属性赋值为null,最后生成虚拟表VT3。在该示例中，保留表是Customers,在步骤2中，只有消费者FISSA别过滤掉了，在步骤3中重新添加到虚拟表VT3中，FISSA的Orders相关属性为null。

   步骤4：应用where筛选器：

　　　　对上一步虚拟表VT3中所有的行应用where筛选器，只有满足where筛选条件<where_condition>的行才会被插入到虚拟表VT4中。对于包含outer join 子句的查询中，有一点非常让人迷惑，逻辑表达式到底应该加在on筛选器后面，还是应该加载where筛选器的后面，两个的主要区别是在on筛选器是在添加外部行之前执行，where筛选器是在添加外部行之后执行，on筛选器对保留表中的行移除不是最终的，因为在步骤3中会重新添加回来，而where筛选器则是最终的。牢记这一点将会帮助你做出正确的选择。（只有在使用外部联接时，on和where才会存在这种逻辑差别，当使用内部联接时，步骤3将会被跳过）在该示例中，where c.city='Madrid'，虚拟表VT3中所有城市不是Madrid的行被移除，生成虚拟表VT4。

   步骤5：分组

　　　　上一步返回的行被分配到各个组。group by子句中的列的每一个唯一的组合成为一组，生成VT5。如果在查询中指定了group by子句，则在以后的步骤中（having、select），只能使用group by 列表中的列（比如c.customerid）或者聚合函数(比如count(o.orderid)),该限制是因为最终的结果集只为每一个组返回一行。在这个步骤中，两个null值会被分到同一个组。

在该示例中，group by c.customerid 为每个消费者分组。

   步骤6：应用Cube 或者 RollUp选项

　　　　如果指定了cube 或者 rollup，将创建超组，添加到上一步返回的虚拟表VT5中，生成虚拟表VT6，在该示例中未指定cube 和 rollup选项，所以跳过步骤6，在以后的文章中我会详细介绍。

   步骤7：应用Having筛选器

　　　　对上一步返回的组应用having筛选器，只有符合筛选条件<having_condition>的组才会被添加到虚拟表VT6中，having筛选器是为一个应该应用到已分组数据的筛选器。在该示例中，having count(o.orderid)<3  返回订单数小于3的分组，这里置顶count(o.orderid)而不是count(*)，是因为该联接是外部联接，没有订单的消费者也会被添加到结果集，count(*)会把外部行也统计在内，使得无法准确的统计订单数。和其他聚合函数一样Count(<expression>)会忽略null值。

   步骤8：处理select 列表

　　　　尽管select列表是在查询中最先被指定的，但却被放到了第8步处理。处理select列表这一步用于构建最终要返回给调用者的表，select列表中的表达式可以是上一个步骤中虚拟表的基列，也可以是对这些基列的操作，在步骤5以后，只能使用虚拟表中的已有的基列，如果引用原始部分中的列，必须对它进行聚合运算。在该示例中，select c.customerid,count(o.orderid) as numorders 得到虚拟表VT8并返回。

   步骤9：应用distinct子句

　　　　如果在查询中指定了distinct子句，则对上一步返回的虚拟表的数据去除重复的行，并生成虚拟表VT9并返回。在我们的示例中，未使用该子句，跳过步骤9。实际上，如果使用了group by 子句，再使用distinct子句就是多余的，它不会移除任何一行数据。

   步骤10：应用order by 子句：

　　　　按order by 子句中的列，对上一步返回的数据进行排序，生成游标VC10。这一步也是唯一一步，可以使用select子句中的列的别名的步骤。因为这一步返回的是游标而不是表，所有使用了order by子句的查询不能用作表表达式（视图、内联函数、派生表、子查询和公用表表达式），比如select * from (select customerid,orderid from orders order by orderid) as d  ，执行这个查询会产生一个错误（除非另外还指定了 TOP、OFFSET 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。）。所以要记住，除非确实需要有序行，否则不要指定order by 子句，排序是需要成本的。order by 这一步会将连个null值排在一起。在该示例中，order by numorders。

   步骤11：应用top选项

　　　　指定要返回的行数或者百分比，top的输入必须是一个常量，从上一步返回的游标VC10最前面选取top指定的行数，生成虚拟表VT11并返回给调用者。这一步是根据行的物理顺序确定哪些行属于优先请求的。如果在查询中指定了order by 子句以及唯一的order by 列表，结果将是确定的。也就是说，只有一种正确的结果，并且结果中包含基于特定顺序被优先请求的行数。同样的，如果指定了order by子句和不唯一的order by 列表，但是top 选项指定了with ties ,结果也是确定的，Sql Server 检查返回的最后一行，并检查返回表中所有与最后一行具有相同排序值得其他行。

　　　　然而，如果指定了不唯一的order　by 列表且未指定with ties 选项，或者就根本没有使用order by 子句，带有top的查询结果将是不确定的，也就是说，返回的行正好是物理上最先访问到的行，因此它可能产生不同的结果，但都被认为是正确的。如果你想保证结果的确定性，top 查询必须指定唯一的order by 列表或者指定with ties选项。该示例中并未指定top，则跳过该步骤。

   以上就是逻辑查询处理的各个阶段，熟悉了该语言的这些步骤，有助于得到高效的解决方案并解释所做的选择。记住，掌握基础是关键。