T-SQL查询的逻辑工作原理

t-sql查询的逻辑工作原理
 

很多朋友都会使用t-sql编写查询，但实际上对于其工作原理大多知之甚少。下面我们来分析一下查询的逻辑工作原理，也就是说一个查询到底是怎么一步一步的工作，最终给我们展示数据的。

认真观察下面这个图。想想看，这是我们常写的语句，你真的有没有想过哪个部分是先执行，而哪是个后面执行的呢

on,where,having是三种筛选的子句。正因为他们是在select语句执行之前执行的，所以他们所用的表达式是不可以为select子句中重命名的列的

他们还有一个共同特征，如果比较运算符的一个部分为null，则既不返回true也不返回false，而是返回unknown

 

 

下面我们用一个实例来讲解这些步骤

第一步：准备实验环境和数据

set nocount on;
use tempdb;
go
if object_id('dbo.orders') is not null
  drop table dbo.orders;
go
if object_id('dbo.customers') is not null
  drop table dbo.customers;
go
create table dbo.customers
(
  customerid  char(5)  not null primary key,
  city  varchar(10) not null
);

insert into dbo.customers(customerid, city) values('fissa', 'madrid');
insert into dbo.customers(customerid, city) values('frndo', 'madrid');
insert into dbo.customers(customerid, city) values('krlos', 'madrid');
insert into dbo.customers(customerid, city) values('mrphs', 'zion');

create table dbo.orders
(
  orderid  int  not null primary key,
  customerid char(5)  null  references customers(customerid)
);

insert into dbo.orders(orderid, customerid) values(1, 'frndo');
insert into dbo.orders(orderid, customerid) values(2, 'frndo');
insert into dbo.orders(orderid, customerid) values(3, 'krlos');
insert into dbo.orders(orderid, customerid) values(4, 'krlos');
insert into dbo.orders(orderid, customerid) values(5, 'krlos');
insert into dbo.orders(orderid, customerid) values(6, 'mrphs');
insert into dbo.orders(orderid, customerid) values(7, null);

 

以上语句是在tempdb中创建了两个表，分别表示客户信息和订单信息。订单表与客户表是通过customerid进行连接的。我们也分别为他们输入了一些数据。如下面所示

 

如你所见，这两个表很简单的

 

第二步：准备一个查询做测试

/*
一个测试查询，检索那些订单个数小于3的客户，并且按订单总数排序（升序）
*/
select c.customerid, count(o.orderid) as numorders
from dbo.customers as c
  left outer join dbo.orders as o
  on c.customerid = o.customerid
where c.city = 'madrid'
group by c.customerid
having count(o.orderid) < 3
order by numorders;

同样是很简单的一个查询，它返回是订单总数小于3的客户。如下所示

很多朋友研究到这里就停止了，不过，我们下面要详细的分析这个查询的结果是怎么出来的

/*第一步：处理from子句，把来源的表进行cross join（笛卡尔乘积）
我这里把结果做一个生成表查询，写到一个临时表（vt1)中去
*/
select c.customerid as customer,c.city,o.*  into #vt1 from dbo.customers c,dbo.orders  o
select * from #vt1
--返回28行数据（4*7)

 

 

--第二步：处理on子句，只把那些两个表的customerid匹配的行找出来，我把它们放到vt2中去
select temp.* into #vt2 from (select * from #vt1 where customer=customerid) temp
select * from #vt2
--返回6行数据

 

 

--第三步：根据join语句的类型，决定是否要添加行到vt2中去，例如如果是left  join的话，那么就要检查坐边的表（我们这里是customers表）的连接键值是否都存在，如果不存在就要去添加到vt2中
select temp.* into #vt3 from
(select * from #vt2
union all
select customerid,city,null,null from dbo.customers c where not exists(select distinct customer from #vt2 where customer=c.customerid)) temp
select * from #vt3
--返回7行数据，其中有一个客户，因为没有订单，这一步中被添加进来。它的orders的记录被标记为null

 

--第四步：处理where 子句，对vt3的结果集进行过滤，我们的条件是city=madid
select temp.* into #vt4 from
(select * from #vt3 where city='madrid') temp
select * from #vt4
--返回6行数据，因为有一个客户不是这个城市的

 

 

--第五步：处理group子句,进行分类汇总
select temp.* into #vt5 from
(select customer,count(orderid)  as orderidcount,count(city) as citycount,count(customerid)  as customeridcount from #vt4 group by customer) temp
select * from #vt5
--返回3行数据，根据客户分组，统计了订单的个数
--这里会不会去统计其他列的汇总呢

 

更正：这一步，逻辑上的数据结构是下面这样的，也就是说其实并没有计算。而是分组

 

 

--因为没有with rollup和with cube语句，所以跳过第六步，进入having子句的处理

--第六步：处理having子句，对group之后的结果进行筛选，我们这里的条件是orderidcount<3
select temp.* into #vt6 from
(select * from #vt5 where orderidcount<3) temp
select * from #vt6
--返回2行数据

 

--第七步：使用select 的字段列表过滤结果集
select #vt6.customer,#vt6.orderidcount as  numorders  into #vt7 from #vt6
select * from #vt7
--还是2行数据，只不过只有两个列了

 

select 返回的结果在内部其实是一个游标集，本身并没有顺序，它就好比是一个集合。

 

--第八步：跳过distinct,进行orderby操作
select #vt7.* into #vt8 from #vt7 order by #vt7.customer
select * from #vt8
--返回2行数据，经过排序

 

--这个查询结束演示。完整的8个步骤