你真的会玩SQL吗？之逻辑查询处理阶段

最近要对数据库进行优化，但由于工作项目中已经很少亲自写SQL而且用的都不是很复杂的语句，所以有些生疏了，于是翻翻N年前的笔记资料，想以此来记录回顾总结一些实用的SQL干货让大家来学习，若有不对之处可提出。

记得刚出来行走江湖的时候也是只会增、删、改、查四大法宝，一般公司没有多少复杂的业务，所以就够用了。但后来看着大神会写个几百行的SQL存储过程就感觉自己是不是弱爆了。

如今是大数据的时代，对数据的处理要求越来越重视，要出各种数据报表，因此百万数据处理速度，数据库明显比后台逻辑处理的优势不是一个别。

下面进入正题，写了多年的SQL，你真的玩会了SQL吗？

在此我想再次提示一个数据处理的中心思想，SQL数据处理是集合思维，不要用逻辑思维来思考。

文中的示例来自自己的积累和TSQL2008技术内幕。

基础知识普及

对于教条式的定义请自己去查，此处不会涉及到文邹邹的知识，但还是强调一下基础的重要性，即使你理解了所有的概念，但当组合起来用时也会一头雾水。

逻辑查询处理阶段

在以上的10个处理步骤中, 每一步的处理都生成一个虚拟表来作为下一步的输入. 虚拟表对于调用者或输出查询来说是不存在的, 仅在最后步骤生成的表才会返回给调用者或者输出查询. 如果某一子句没有出现在SQL语句中, 这一步就被简单跳过..

这10个具体步骤是:
1．FROM: from子句中的两个表首先进行交叉连接(笛卡尔积), 生成虚拟表VT1。
2．ON: on条件作用在VT1上, 将条件为True的行生成VT2。
3．OUTER: 如果outer join被指定, 则根据外连接条件, 将左表or右表or多表的未出现在VT2查询结果中的行加入到VT2后生成VT3。
4．WHERE: VT3表中应用Where条件, 结果为真的行用来生成VT4。
5．GROUP BY: 根据Group by指定的列, 将VT4的行组织到不同的组中, 生成VT5。
6．CLUB|ROLLUP: 超级组(分组之后的分组)被添加到VT5中, 生成VT6。
7．HAVING: Having用来筛选组, VT6上符合条件的组将用来生成VT7。
8．SELECT: select子句用来选择指定的列, 并生成VT8。
9．DISTINCT: 从VT8中删除重复的行后, VT9被生成。
10．ORDER BY: 根据Order by子句, VT9中的行被排序, 生成游标10。

注意事项:

第一步中FROM: 需要对两表同时存在的列添加前缀, 以免混淆.

第二步中ON: 在SQL特有的三值逻辑(true,false,unknown)中, unkown的值也是确定的, 只是在不同情况下有时为true, 有时为false. 一个总的原则是: unknown的值非真即假, 非假即真. 也就是时说, unknown只能取true和false里面的一个值, 但是unknown的相反还是unknown.如:

在ON、WHERE和HAVING中做过滤条件时, unknown看做false;

在CHECK约束中, unknown被看做是true;

在条件中, 两个NULL的比较结果还是Unknown.

在UNIQUE和PRIMARY KEY约束、排序和分组中, NULL被看做是相等的. 例如Group by 将null分为一组, 而order by将所有null排在一起.

第三步中OUTER: 如果多余两张表, 则将VT3和FROM中的下一张表再次执行从第一步到第三步的过程.

第四步中WHERE: 由于此刻没有分组, 也没有执行select所以, where子句中不能写分组函数, 也不能使用表的别名. 并且, 只有在外连接时, on和where的逻辑才是不同的, 因此建议连接条件放在on中.

第五步中GROUP BY: 如果查询中包含Group by 子句, 那么所有的后续操作(having, select等)都是对每一组的结果进行操作.

Group by子句中可以使用组函数, 在Sql 2000中一旦使用组函数, 其后面的步骤将都不能处理, 而在

Sql2005中没有这个限制.

第六步不常用, 略过.

第七步中HAVING: having表达式是仅有的分组条件. 注意: count(*)不会忽略掉null, 而count(field)会; 此外分组函数中不支持子查询做输入.

第八步中SELECT: 如果包含Group By子句, 那么在第5步后将只能使用Group By子句中出现的列, 如果要使用其他原始列则, 只能使用组函数.

另外, select在第八步才执行, 因此别名只能第八步之后才能使用, 并且只能在order by中使用.

第九步中DISTINCT: 当使用Group By子句时, 使用Distinct是多余的, 他不会删除任何记录.

第十步中ORDER BY: 按Order by子句指定的列排序后, 返回游标VC10.

别名只能在Order by子句中使用.

如果定义了Distinct子句, 则只能排序上一步中返回的表VT9, 如果没有指定Distinct子句, 则可以排序不再最终结果集中的列. 例如: 如果不加Distinct则Order by可以访问VT7和VT8中的内容.

这一步最不同的是它返回的是游标而不是表, Sql是基于集合论的, 集合中的元素师没有顺序的, 一个在表上引用Order by排序的查询返回一个按照特定特定物理顺序组织的对象—游标. 所以对于视图、子查询、派生表等均不能将order by结果作为其数据来源.

建议: 使用表的表达式时, 不允许使用order by子句的查询, 因此除非你真的要对行排序, 否则不要使用order by 子句.

内容为 RJ 写的，逻辑非常清楚，值得花点时间理解，再次强调是因为复杂的集合数据处理过程中会得到不是你想要的结果，这时就要你自己脑袋当SQL处理器来推出结果查出问题，可能大多数写了几年的SQL都还没弄明白，但到了用时还是提前理解下，非常重要。

练习

此后用到的用例数据库是SQL2008里面的

/*1.返回来自美国的客户，并为每个客户返回其订单总数和商品交易总数量。
涉及到表：Sales.Customers表、Sales.Orders表，以及Sales.OrderDetails表。
期望的输出：
*/
custid      numorders   totalqty
----------- ----------- -----------
32          11          345
36          5           122
43          2           20
45          4           181
48          8           134
55          10          603
65          18          1383
71          31          4958
75          9           327
77          4           46
78          3           59
82          3           89
89          14          1063
 1参考SQL：
--answer:
select c.custid,count(distinct o.orderid) as 'numorders',sum(od.qty) as 'totalqty'
from Sales.Customers as c
join Sales.Orders as o
on c.custid=o.custid
join Sales.OrderDetails as od
on o.orderid=od.orderid
where c.country='USA'
group by c.custid
/*
1.将表Sales.Customers别名为c和表Sales.Orders别名为o应用ON筛选器以custid为条件内连接，生成虚拟表VT1，
2.将虚拟表VT1和表Sales.OrderDetails应用ON筛选器以orderid为条件内连接，生成虚拟表VT2，
3.对上一步返回的虚拟表中的所有行应用where筛选器返回满足条件c.country='USA'的虚拟表VT3，
4.应用group by子句将数据以c.custid列分组
5.处理select列表，去掉重复o.orderid再用count统计个数返回别名为numorders的列，统计od.qty列别名totalqty
*/
/*2：返回客户及其订单信息，包括没有下过任何订单的客户。
涉及到表：Sales.Customers和Sales.Orders表。
期望的输出（按简略的格式显示）：
*/
custid      companyname     orderid     orderdate
----------- --------------- ----------- ------------------------
85          Customer ENQZT  10248       2006-07-04 00:00:00.000
79          Customer FAPSM  10249       2006-07-05 00:00:00.000
34          Customer IBVRG  10250       2006-07-08 00:00:00.000
84          Customer NRCSK  10251       2006-07-08 00:00:00.000
...
73          Customer JMIKW  11074       2008-05-06 00:00:00.000
68          Customer CCKOT  11075       2008-05-06 00:00:00.000
9           Customer RTXGC  11076       2008-05-06 00:00:00.000
65          Customer NYUHS  11077       2008-05-06 00:00:00.000
22          Customer DTDMN  NULL        NULL
57          Customer WVAXS  NULL        NULL
2参考SQL：
--answer:
select c.custid,c.companyname,o.orderid,o.orderdate
from Sales.Customers as c
left join Sales.Orders as o
on c.custid=o.custid
/*
1.将表Sales.Customers别名为c和表Sales.Orders别名为o应用ON筛选器以custid为条件左外连接，生成虚拟表VT1，
2.添加外部行，外部行中非保留表中的属性被赋值为NULL，生成虚拟表VT2
3.处理select列表，查找出c.custid,c.companyname,o.orderid,o.orderdate生成虚拟表VT3
*/
/*3：返回值2007年2月12日下过订单的客户，以及他们的订单。同时也返回在2007年2月12日没有下过订单的客户。
涉及到表：Sales.Customers表和Sales.Orders表。
期望的输出（按简略格式显示）：
*/
custid      companyname     orderid     orderdate
----------- --------------- ----------- -----------------------
72          Customer AHPOP  NULL        NULL
58          Customer AHXHT  NULL        NULL
25          Customer AZJED  NULL        NULL
18          Customer BSVAR  NULL        NULL
91          Customer CCFIZ  NULL        NULL
...
33          Customer FVXPQ  NULL        NULL
53          Customer GCJSG  NULL        NULL
39          Customer GLLAG  NULL        NULL
16          Customer GYBBY  NULL        NULL
4           Customer HFBZG  NULL        NULL
5           Customer HGVLZ  10444       2007-02-12 00:00:00.000
42          Customer IAIJK  NULL        NULL
34          Customer IBVRG  NULL        NULL
63          Customer IRRVL  NULL        NULL
73          Customer JMIKW  NULL        NULL
15          Customer JUWXK  NULL        NULL
...
21          Customer KIDPX  NULL        NULL
30          Customer KSLQF  NULL        NULL
55          Customer KZQZT  NULL        NULL
71          Customer LCOUJ  NULL        NULL
77          Customer LCYBZ  NULL        NULL
66          Customer LHANT  10443       2007-02-12 00:00:00.000
38          Customer LJUCA  NULL        NULL
59          Customer LOLJO  NULL        NULL
36          Customer LVJSO  NULL        NULL
64          Customer LWGMD  NULL        NULL
29          Customer MDLWA  NULL        NULL
...
3参考SQL：
--answer:
select c.custid,c.companyname,o.orderid,o.orderdate
from Sales.Customers as c
left join Sales.Orders as o
on c.custid=o.custid
and o.orderdate='2007-2-12'
/*
1.将表Sales.Customers别名为c和表Sales.Orders别名为o应用ON筛选器以custid和o.orderdate='2007-2-12'为条件左外连接，生成虚拟表VT1，
2.添加外部行，外部行中非保留表中的属性被赋值为NULL，生成虚拟表VT2
3.处理select列表，从虚拟表VT2中查找出c.custid,c.companyname,o.orderid,o.orderdate生成虚拟表VT3
*/