快捷導(dǎo)航

Sql學(xué)習(xí)第一天——SQL 練習(xí)題(建表/sql語句)

更新時間：2013年03月22日 11:19:34 作者：

來自Madrid且訂單數(shù)少于3的消費者,針對這個要求作出以下:建表做題分析以及sql語句的寫法，感興趣的朋友可以參考下哈，希望可以幫助到你

題目:來自Madrid且訂單數(shù)少于3的消費者

建表:

 
set nocount on --當(dāng) SET NOCOUNT 為 ON 時，不返回計數(shù)（表示受 Transact-SQL 語句影響的行數(shù)）。當(dāng) SET NOCOUNT 為 OFF 時，返回計數(shù) 
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); 

------------------------------------------------------------------------------------------------------------------------------

做題分析：

復(fù)制代碼代碼如下:

 
select customerid as 消費者,count(customerid) as 訂單數(shù) 
from dbo.Orders 
where customerid in ( 
select customerid 
from dbo.Customers 
where city = 'Madrid') 
group by customerid 
having count(customerid) < 3 

結(jié)果如圖所示：

--第一次想到的答案,突然發(fā)現(xiàn)少了一個來自Madrid的FISSA訂單,FISSA訂單數(shù)量為0,所以在Orders表中沒有出現(xiàn),所以上面的寫法會少一個.

--推翻了上面的答案,又想到了用表的連接,而用內(nèi)連接出現(xiàn)的情況會和上面的一樣,所以我選擇了左連接,如下:

復(fù)制代碼代碼如下:

 
select C.customerid as 消費者,count(O.customerid) as 訂單數(shù) 
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 

結(jié)果如圖所示：

--查詢發(fā)現(xiàn)是正確的。

--分析查看不帶條件的左連接

復(fù)制代碼代碼如下:

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

復(fù)制代碼代碼如下:

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

結(jié)果如圖所示：

--書中給的標(biāo)準(zhǔn)答案是：

復(fù)制代碼代碼如下:

 
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 

結(jié)果如圖所示：