SQL 語言 題目
FLAT (FlatNo, Area, FlatSize, OwnerNo)
OWNER (OwnerNo, OwnerName, OwnerTel)
Show the owner number and the owner name of the owners who own more than one
flat.
回答 (4)
select count(OwnerNo), OwnerNo, OwnerName from FLAT,OWNER where
FLAT.OwnerNo = OWNER.OwnerNO group by OwnerNo having count(OwnerNo)>1;
參考: me
解說;
第一件事要做係找出所有owner有多個一個flat,句法係咁
select ownerNo from flats group by ownerNo having count(*)>1
注意係不可以用where,因為count並不是在table內的資料,而是全部record找出才知,所以要用having。
跟住,呢句好易,要找出OwnerNo,OwnerName,簡單之極
select OwnerNo,OwnerName from Owner
搞掂。不,仲爭一樣野,只要多過一個flat,所以後面加
where OwnerNo in (XXX)
而xxx則是剛才第一部份那句 select。
完整句子就係
select OwnerNo,OwnerName from Owner
where OwnerNo in (select ownerNo from flats group by ownerNo having count(*)>1
)
你答那句where flatNo>1錯到離譜,因為flatNo係房子的編號,不是數目。而且很可能是char,不可以>1。
Newsbook一間好網存公司,可辦妥,網頁寄存,網頁設計,伺服器租用/托管,建立論壇,域名申請,開設公司電郵,價錢合理,有多個月費計劃選擇,上
www.newsbook.net就可查詢更多計劃內容看
留意Flat No係Flat個表度,
唔可以Select左Owner表之後直接換表,
要加子查詢先得。
收錄日期: 2021-04-16 16:28:13
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20131217000051KK00084
檢視 Wayback Machine 備份