欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時(shí)間:2013年03月22日 11:19:34   作者:  
來(lái)自Madrid且訂單數(shù)少于3的消費(fèi)者,針對(duì)這個(gè)要求作出以下:建表 做題分析以及sql語(yǔ)句的寫(xiě)法,感興趣的朋友可以參考下哈,希望可以幫助到你

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

建表:

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

set nocount on --當(dāng) SET NOCOUNT 為 ON 時(shí),不返回計(jì)數(shù)(表示受 Transact-SQL 語(yǔ)句影響的行數(shù))。當(dāng) SET NOCOUNT 為 OFF 時(shí),返回計(jì)數(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 消費(fèi)者,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)少了一個(gè)來(lái)自Madrid的FISSA訂單,FISSA訂單數(shù)量為0,所以在Orders表中沒(méi)有出現(xiàn),所以上面的寫(xiě)法會(huì)少一個(gè).

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

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

select C.customerid as 消費(fèi)者,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é)果如圖所示:

--書(shū)中給的標(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é)果如圖所示:

--書(shū)中給的只是多了一個(gè)order by 進(jìn)行定義了排序方式(以numorders這一列的升序進(jìn)行排序)

相關(guān)文章

最新評(píng)論