34,597
社区成员
发帖
与我相关
我的任务
分享【T-MAC学习笔记4之--SQL中逻辑查询处理的各个阶段】
今天我来谈下有关SQL中逻辑查询处理的各个阶段（自己搞的例子，不对的欢迎指正哦）
后面还有
[code=SQL]SQL不同于其他的编程语言的最大最大特征有3个吧，
一个是它是面向集合的编程思想，第二个是三值逻辑（这个后面会说到），还有一个就是今天主要要说的查询元素的逻辑处理次序。
请看一个基本查询的逻辑过程：
(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(10) ORDER BY <order_by_list>
大家可以看到 这里的运行步骤不是像一般的编程 一句句从上往下 它是跳动的 有活力的
这里提前说下 每一步都会产生一个虚拟的表（也可能是游标，下面会提到），作为下一个步骤的输入。大家最后看到的结果其实就是最后一个虚拟表了。
好了，下面我开始具体阐释每个步骤：
测试环境：
--题目要求：求出学生最低科目成绩不低于90分且年龄在7岁以上的学生姓名
create table #student(s# int,sname varchar(10),age int)
create table #study (s# int, c# char(1),score int)
insert #student
1,'xiaozhu',10 union all select
2,'xiaomao' ,9union all select
3,'xiaozhe' ,7union all select
4,'xiaophai',8 union all select
5,'xiaoduo',9
insert #study select
1,'A',99 union all select
1,'B',90 union all select
1,'C',99 union all select
2,'A',99 union all select
2,'b',99 union all select
2,'c',98 union all select
3,'A',99 union all select
3,'b',92 union all select
3,'c',91 union all select
3,'d',90 union all select
4,'A',88 union all select
4,'B',96
--SQL语句
select top 1 sname,MIN(score) as minsocre
from #student s left outer join #study sc
on s.s#=sc.s#
where age>7
group by sname
having MIN(score)>=90
order by minsocre
/*
sname minsocre
---------- -----------
xiaozhu 90
*/
(1)：执行笛卡尔积（CROSS JOIN ）
大家这里可能会觉得奇怪 明明上面的2后面只有一个表怎么笛卡尔积呢？其实不是这样的，它做的就是要将FROM最前面2表进行CROSS JOIN（因为可能多表）
在我的例子里就是#student 和#study表进行笛卡尔积操作(这个笛卡尔积相信大家都会)，这里就会出现第一个虚拟表，我们叫它VTB1
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
1 xiaozhu 10 2 A 99
1 xiaozhu 10 2 b 99
1 xiaozhu 10 2 c 98
1 xiaozhu 10 3 A 99
1 xiaozhu 10 3 b 92
....
....
.....太多了，不占空间
5 xiaoduo 9 2 b 99
5 xiaoduo 9 2 c 98
5 xiaoduo 9 3 A 99
5 xiaoduo 9 3 b 92
5 xiaoduo 9 3 c 91
5 xiaoduo 9 3 d 90
5 xiaoduo 9 4 A 88
5 xiaoduo 9 4 B 96
*/
(2)应用ON筛选器（联接条件）
说到这里，首先得说下 SQL用于查询的有三个筛选器，分别是ON，where，having。
这里的ON筛选器就是用VTB1作为输入，再利用ON后面的联结条件进行筛选，生成VTB2。
sql编程第二特征：三值逻辑：它其实就是所谓的TRUE FALSE UNKOWN
主要来说这个UNKOWN 它这逻辑值和NULL在一起就会”出事“。凡是NULL参与的比如 NULL>42, NULL-8>9 这些结果都是UNKOWN
UNKOWN 的逻辑结果在不同的环境下是被不同的方式处理的：如
在三个筛选器上，它是会被当做FALSE处理；
在CHECK约束上是会被当成TRUE来处理，check(col>8) 你插入的COL为NULL，因为NULL>8 的结果不就是UNKOWN ，所以是可以插入的。
在筛选器上的2个NULL比较将是FALSE 但是在UNIQUE约束，分组，排序上2个NULL是等价的。
好了，大概就这些吧 。我贴下VTB2的结果：
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
2 xiaomao 9 2 A 99
2 xiaomao 9 2 b 99
2 xiaomao 9 2 c 98
3 xiaozhe 7 3 A 99
3 xiaozhe 7 3 b 92
3 xiaozhe 7 3 c 91
3 xiaozhe 7 3 d 90
4 xiaophai 8 4 A 88
4 xiaophai 8 4 B 96
*/
(3)添加外部行（outer row）
这里首先要知道一个地方：如果你的表没有涉及到OUTER JOIN 那么就不需要这步，这第三步就是为外部联接准备的。
我的测试环境下为了说明这个地方，加了LEFT JOIN ，它这里起到效果简单来说就是拿回刚才第二步损失的部分记录，因为你也看到了第五个学生'xiaoduo'
在虚拟表VTB2中没出现，这里完成后 它又回来了。vtb3
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
2 xiaomao 9 2 A 99
2 xiaomao 9 2 b 99
2 xiaomao 9 2 c 98
3 xiaozhe 7 3 A 99
3 xiaozhe 7 3 b 92
3 xiaozhe 7 3 c 91
3 xiaozhe 7 3 d 90
4 xiaophai 8 4 A 88
4 xiaophai 8 4 B 96
5 xiaoduo 9 null null null
*/
PS: 如果你涉及到三表及三表以上操作，你就可以将这个VTB3表和你FROM后面的第三个表进行1-3步骤的重复操作，依次类推，最后再拿出一个虚拟表就OK
(4)应用WHERE筛选器
这里看起来很简单，只要将VTB3表 用WHERE 后面的条件过滤下得到VTB4表就可以。其实还是有很多知识在这的。
首先，在这我知道了为什么这里不可以用聚合函数了，因为数据在前面那几步还没被分组了，怎么可以用聚合函数呢？也知道了不可以用那些你在select
后面定义的列别名，理由很简单，你都没操作那步呢。
再者，我们考虑一个问题：我们那个条件age>7 可以写在ON后面么？答案在这里是否定的！
A：from #student s left outer join #study sc
on s.s#=sc.s#
where age>7
B：from #student s left outer join #study sc
on s.s#=sc.s# and s.age>7
这里2种情况形成的虚拟表是这样的
A:
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
2 xiaomao 9 2 A 99
2 xiaomao 9 2 b 99
2 xiaomao 9 2 c 98
4 xiaophai 8 4 A 88
4 xiaophai 8 4 B 96
5 xiaoduo 9 null null null
*/
B:也就是VTB3
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
2 xiaomao 9 2 A 99
2 xiaomao 9 2 b 99
2 xiaomao 9 2 c 98
3 xiaozhe 7 3 A 99
3 xiaozhe 7 3 b 92
3 xiaozhe 7 3 c 91
3 xiaozhe 7 3 d 90
4 xiaophai 8 4 A 88
4 xiaophai 8 4 B 96
5 xiaoduo 9 null null null
*/
大家可以看到，如果把AGE>7写ON后面 这个出来的临时表比写WHERE后面多了那个年龄是7岁的xiaozhe，这显然不是我们想要看到的。
原因就是因为你这个ON后面的条件是在第二步就做完了，这个时候你过滤掉了7岁的xiaozhe 可是你第三步外联接下 它就又可以回来的，
而如果写where 后面就可以防止这种情况发生了。现在可以给出结论了，写2个地方区别就在于ON过滤掉的记录可以再恢复，但是WHERE过滤掉的就回不来了。
PS：我这说的这种情况也只是在有OUTER JOIN的情况下才会出现的，如果是INNER join之类的就不会出现差别，因为它不存在第三步。
大家可以将语句换成
select sname,MIN(score) as minsocre --这里注意把TOP 1去掉 不然看不出来了
from #student s left outer join #study sc
on s.s#=sc.s# and age>7
group by minsocre
having MIN(score)>=90
order by minsocre
/*
sname minsocre
---------- -----------
xiaomao 98
xiaozhu 90
*/
为了清楚说明，我再写下这步产生的VTB4表：
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
2 xiaomao 9 2 A 99
2 xiaomao 9 2 b 99
2 xiaomao 9 2 c 98
4 xiaophai 8 4 A 88
4 xiaophai 8 4 B 96
5 xiaoduo 9 null null null
*/
(5) 分组
这步就是分组，你确定你要分组的对象，它唯一的。然后根据这个基础对象，把组分好。例子里就是学生的姓名(假设没出现重名的).
这里的VTB5书上的意思就是其实由2部分组成：、
1是实际组构成的成组部分（xiaozhu,xiaomao,xiaophai,xiaoduo）这几个基础对象吧，
2就是本来那个VTB4传下来的基础数据。
这里我们就可以看出一个东西：你在SELECT后面的列 要么就包含在聚合函数里面要么就成为GROUP BY 的基础对象。
例如:你直接select sname,score 因为你分组了 你这组只能返回关于这个组的一条信息。那么对于XIAOZHU这组来说有三个SCORE，你让它如何返回？
所以我们要用聚合函数来限定这个SCORE，如MIN(SCORE)。。。。。。
这里再提一个小地方：原本在2000里面你如果GROUP BY MIN(SCORE) 你在select 后面用SELECT MIN(SCORE)+1 as newscore 这样是不可以的。
就是说：你不能对GROUP BY 的基础对象做处理，除非他是表里的列。但是2005里面你可以放心使用我上面说的情况。
由于GROUP BY ALL是非标准的遗留物，所以不推荐，我也就不说了。。
VTB5：
/*
s# sname age s# c# score
----------- ---------- ----------- ----------- ---- -----------
1 xiaozhu 10 1 A 99
1 xiaozhu 10 1 B 90
1 xiaozhu 10 1 C 99
------------------------------------------------------------
2 xiaomao 9 2 A 99
2 xiaomao 9 2 b 99
2 xiaomao 9 2 c 98
-------------------------------------------------------
4 xiaophai 8 4 A 88
4 xiaophai 8 4 B 96
-------------------------------------------------------
5 xiaoduo 9 null null null
*/后面还有
...全文
请发表友善的回复…
发表回复
wuwuman 2010-03-11
- 打赏
- 举报
plglenn1x都是同一个人吧，太恶心了
mdsorphen 2010-01-19
- 打赏
- 举报
MARK,有助于我理清对SQL语句思路。谢谢！
bancxc 2009-11-04
- 打赏
- 举报
盖楼机器
whisky_sour 2009-11-04
- 打赏
- 举报
很有收获,看看高手的经验自己可以少走很多弯路
rucypli 2009-11-04
- 打赏
- 举报
d
feixianxxx 2009-11-04
- 打赏
- 举报
。。。犀利的 顶楼
plglenn35 2009-11-01
- 打赏
- 举报
d
plglenn34 2009-11-01
- 打赏
- 举报
d
plglenn32 2009-11-01
- 打赏
- 举报
d
plglenn31 2009-11-01
- 打赏
- 举报
d
plglenn30 2009-11-01
- 打赏
- 举报
d
plglenn29 2009-11-01
- 打赏
- 举报
d
plglenn28 2009-11-01
- 打赏
- 举报
d
plglenn27 2009-11-01
- 打赏
- 举报
d
plglenn14 2009-11-01
- 打赏
- 举报
d
plglenn13 2009-11-01
- 打赏
- 举报
d
plglenn12 2009-11-01
- 打赏
- 举报
d
plglenn11 2009-11-01
- 打赏
- 举报
d
plglenn10 2009-11-01
- 打赏
- 举报
d
plglenn9 2009-11-01
- 打赏
- 举报
d
加载更多回复（172）
