set nocount on --当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当 SET NOCOUNT 为 OFF 时，返回计数 
use SY 
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 values('FISSA','Madrid'); 
insert into dbo.Customers values('FRNDO','Madrid'); 
insert into dbo.Customers values('KRLOS','Madrid'); 
insert into dbo.Customers values('MRPHS','Zion'); 

create table dbo.Orders 
( 
orderid int not null primary key , 
customerid char(5) null references customers(customerid) 
) 
insert into dbo.Orders values(1,'FRNDO'); 
insert into dbo.Orders values(2,'FRNDO'); 
insert into dbo.Orders values(3,'KRLOS'); 
insert into dbo.Orders values(4,'KRLOS'); 
insert into dbo.Orders values(5,'KRLOS'); 
insert into dbo.Orders values(6,'MRPHS'); 
insert into dbo.Orders values(7,null); 

select customerid as 消费者,count(customerid) as 订单数 
from dbo.Orders 
where customerid in ( 
select customerid 
from dbo.Customers 
where city = 'Madrid') 
group by customerid 
having count(customerid) < 3 

select C.customerid as 消费者,count(O.customerid) as 订单数 
from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid 
where C.city= 'Madrid' 
group by C.customerid 
having count(C.customerid) < 3 

select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid 

select * from dbo.Customers as C left join dbo.Orders as O on C.customerid = O.customerid 

select C.customerid , count(O.orderid) as numorders 
from dbo.Customers as C left 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