Sql進修第一天——SQL 演習題(建表/sql語句)。本站提示廣大學習愛好者：（Sql進修第一天——SQL 演習題(建表/sql語句)）文章只能為提供參考，不一定能成為您想要的結果。以下是Sql進修第一天——SQL 演習題(建表/sql語句)正文
標題:來自Madrid且定單數少於3的花費者
建表:
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
成果如圖所示：
--第一次想到的謎底,忽然發明少了一個來自Madrid的FISSA定單,FISSA定單數目為0,所以在Orders表中沒有湧現,所以下面的寫法會少一個.
--顛覆了下面的謎底,又想到了用表的銜接,而用內銜接湧現的情形會和下面的一樣,所以我選擇了左銜接,以下:
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
成果如圖所示：
--書中給的只是多了一個order by 停止界說了排序方法(以numorders這一列的升序停止排序)
