sql server逻辑查询处理

-- =============================================         
-- Author:      余波(杭州)         
-- Create date: 2011/09/20         
-- Description: 逻辑查询处理         
-- =============================================    
----建表并插入测试数据
create table customers
(
	customerid char(5) not null primary key,
	city varchar(10) not null
)
GO

insert into customers
select 'FISSA','Madrid'
union all
select 'FRNDO','Madrid'
union all
select 'KRLOS','Madrid'
union all
select 'MRPHS','Zion'
GO

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

insert into orders 
select 1,'FRNDO'
union all
select 2,'FRNDO'
union all
select 3,'KRLOS'
union all
select 4,'KRLOS'
union all
select 5,'KRLOS'
union all
select 6,'MRPHS'
union all
select 7,NULL

------查询返回来自Madrid且订单数少于3(包括0个订单)的消费者
select a.customerid,COUNT(b.orderid) as numorders from customers a left join orders b
on a.customerid=b.customerid where city='Madrid' group by a.customerid
having COUNT(b.orderid)<3
order by numorders

-- 执行步骤
-- 1、from ,执行两个表的笛卡尔积，生成虚拟表T1，如下
select * from customers a,orders b  ----与这一步类似
/*
customerid	city	orderid	customerid
FISSA	Madrid	1	FRNDO
FISSA	Madrid	2	FRNDO
FISSA	Madrid	3	KRLOS
FISSA	Madrid	4	KRLOS
FISSA	Madrid	5	KRLOS
FISSA	Madrid	6	MRPHS
FISSA	Madrid	7	NULL
FRNDO	Madrid	1	FRNDO
FRNDO	Madrid	2	FRNDO
FRNDO	Madrid	3	KRLOS
FRNDO	Madrid	4	KRLOS
FRNDO	Madrid	5	KRLOS
FRNDO	Madrid	6	MRPHS
FRNDO	Madrid	7	NULL
KRLOS	Madrid	1	FRNDO
KRLOS	Madrid	2	FRNDO
KRLOS	Madrid	3	KRLOS
KRLOS	Madrid	4	KRLOS
KRLOS	Madrid	5	KRLOS
KRLOS	Madrid	6	MRPHS
KRLOS	Madrid	7	NULL
MRPHS	Zion	1	FRNDO
MRPHS	Zion	2	FRNDO
MRPHS	Zion	3	KRLOS
MRPHS	Zion	4	KRLOS
MRPHS	Zion	5	KRLOS
MRPHS	Zion	6	MRPHS
MRPHS	Zion	7	NULL
*/

-- 2、执行on筛选器，生成虚拟表T2
select * from customers a join orders b on a.customerid=b.customerid
/*
customerid	city	orderid	customerid
FRNDO	Madrid	1	FRNDO
FRNDO	Madrid	2	FRNDO
KRLOS	Madrid	3	KRLOS
KRLOS	Madrid	4	KRLOS
KRLOS	Madrid	5	KRLOS
MRPHS	Zion	6	MRPHS
*/
-- 3、执行left join 将外部行添加到虚拟表T2中，形成虚拟表T3
select * from customers a left join orders b on a.customerid=b.customerid
/*
customerid	city	orderid	customerid
FISSA	Madrid	NULL	NULL
FRNDO	Madrid	1	FRNDO
FRNDO	Madrid	2	FRNDO
KRLOS	Madrid	3	KRLOS
KRLOS	Madrid	4	KRLOS
KRLOS	Madrid	5	KRLOS
MRPHS	Zion	6	MRPHS
*/
-- 4、执行where筛选器，生成虚拟表T4
select * from customers a left join orders b on a.customerid=b.customerid where city='Madrid'
/*
customerid	city	orderid	customerid
FISSA	Madrid	NULL	NULL
FRNDO	Madrid	1	FRNDO
FRNDO	Madrid	2	FRNDO
KRLOS	Madrid	3	KRLOS
KRLOS	Madrid	4	KRLOS
KRLOS	Madrid	5	KRLOS
*/
-- 5、执行group by 分组，形成虚拟表T5
from customers a left join orders b on a.customerid=b.customerid where city='Madrid' 
group by a.customerid

-- 6、应用cube或rollup，这边没有该语句
-- 7、执行having筛选语句，形成虚拟表T6
from customers a left join orders b on a.customerid=b.customerid where city='Madrid' 
group by a.customerid having count(b.orderid)<3
-- 8、执行select，形成虚拟表T7
select a.customerid,COUNT(b.orderid) as numorders from customers a left join orders b

on a.customerid=b.customerid where city='Madrid' group by a.customerid
having COUNT(b.orderid)<3
/*
customerid	numorders
FISSA	0
FRNDO	2
*/
-- 9、执行order by字句，形成结果集
select a.customerid,COUNT(b.orderid) as numorders from customers a left join orders b

on a.customerid=b.customerid where city='Madrid' group by a.customerid
having COUNT(b.orderid)<3
order by numorders
/*
customerid	numorders
FISSA	0
FRNDO	2
*/
---select、distinct、order by、top四个的执行顺序应该是
---1、select，2、distinct，3、order by，4、top