課件課后練習(xí)代碼經(jīng)典案例

TTS6.0（ORACLE達(dá)內(nèi)ITORACLESQL列出客戶以及開通的登錄業(yè)務(wù)的數(shù)列出客戶以及他的推薦哪些客戶是推薦列出客戶以及他的推薦列出客戶以及所開通的登錄業(yè)務(wù)的信息(沒有申請登錄業(yè)務(wù)的客戶列出客戶以及開通的登錄業(yè)務(wù)的數(shù)哪些客戶不是推薦哪些UNIX服務(wù)器上沒有os帳號顯示客戶的顯示客戶huangrong的顯示各個(gè)段的客戶數(shù)（沒有客戶的段不用出現(xiàn)在結(jié)果集中顯示各 段的客戶數(shù)（沒有客戶的段的客戶數(shù)為當(dāng)月 時(shí)長為20小時(shí)，單位費(fèi)用漲5分，為40小時(shí)漲3分，其他不變（列出客戶以及他的推薦sun280和sun-server上 登錄業(yè)務(wù)使用了哪些相同的資費(fèi)標(biāo)哪臺UNIX服務(wù)器上沒有開通登錄業(yè)圖-selectfromaccountajoinserviceona.id=selectfromaccountajoinserviceona.id=selectgroupbyselectfromaccountajoinservicesona.id=s.account_idgroupbyselectfromaccountajoinserviceona.id=groupby fromaccounta(selectaccount_id,count(id)fromgroupbyaccount_id)cona.id=c.account_idanda.id=圖-whereid=mender_idfromselectreal_namefromwhereid=1011;結(jié)果集的信息只來自acun表，但包含兩列：客戶和推薦人的，所謂推薦人是指他的帳務(wù)帳號是別人的mendi,假設(shè)huanng的帳務(wù)帳號ID是1，luwuhuang的帳務(wù)帳號ID是，endwhereid=mender_idfromselectreal_namefromwhereid=1011;selectselecta1.real_namefromaccounta1joinaccountmender_id=selecta1.real_namefromaccounta1joinaccountonmender_id,a1.id)selecta1.real_namefromaccounta1joinaccountonmender_id,a1.id)=圖- whereid=mender_idfromselectreal_namefromwhereid=1011;selectdistinctfromaccounta1joinaccountmender_id=圖-沒有推薦人的客戶的特征是他的mender_id的值為null，因此找不到對應(yīng)的帳務(wù)帳號id跟他匹配。外連接的一個(gè)應(yīng)用場景就是讓匹配不上的記錄也出現(xiàn)在結(jié)果集中。選擇accounta1表做fromaccounta1leftjoinaccountmender_id=selecta1.real_namefromaccounta1leftjoinaccountmender_id=列出客戶以及所開通的登錄業(yè)務(wù)的信息(沒有申請登錄業(yè)圖-fromaccountaleftjoinserviceona.id=selectfromaccountaleftjoinserviceona.id=圖-selectfromaccountaleftjoinserviceona.id=selectgroupbyselectfromaccountaleftjoinservicesona.id=s.account_idgroupby圖-selectfromaccounta1joinaccountona1.idselectfromaccounta1leftjoinaccountselectfromaccounta1leftjoinaccountona1.id就用它們的共同特征，匹配表accounta2的非空列為null了。selectfromaccounta1leftjoinaccountona1.idwherea2.idis圖-selectunix_hostfromwhereos_username=selectselectidfromselecth.idfromhosthleftjoinserviceonh.id=ands.os_username='weixb'wheres.idisnull;顯示客戶的圖-round((sysdate-round((sysdate-round((sysdate-birthdate)/365round((sysdate-birthdate)/365)betweenlowageandselectfromaccountajoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandPAGE11PAGE11.1問題圖-round((sysdateround((sysdate-round((sysdate-birthdate)/365)betweenround((sysdate-birthdate)/365)betweenlowageandandreal_name=selectfromaccountajoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandhiageandreal_name='huangrong';圖-selectfromaccountajoinage_segmentonround((sysdate-birthdate)/365)selectfromaccountajoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandgroupbygroupbyselectfromaccountajoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandgroupby圖-selectfromaccountarightjoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandselectfromaccountarightjoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandgroupbygroupbyselectfromaccountarightjoinage_segmentonround((sysdate-birthdate)/365)betweenlowageandgroupby變（用UNIONALL實(shí)現(xiàn)）圖-selectbase_duration,base_cost,unit_cost+fromwherebase_durationselectbase_duration,base_cost,unit_cost+fromwherebase_duration=selectbase_duration,base_cost,unit_costfromwherebase_durationselectbase_duration,base_cost,unit_costfromwherebase_duration=selectselectfromwherebase_durationnotinselectbase_duration,base_cost,unit_costfrom0.05wherebase_duration=unionselect+fromwherebase_duration=unionselectfromwherebase_durationnotinunionselectfromwherebase_durationis圖- mender_id=a2.id，那么沒有推薦人的客戶不會 mender_idisnull獲得的。將兩部分結(jié)果集用unionall拼接在一起，問題解決。selectfromaccounta1joinaccountunionmender_id=selectfrommender_idis圖-fromhosthjoinservicesonhfromhosthjoinservicesonh.id=s.unix_=fromhosthjoinservicefromhosthjoinservicesonh.id=s.unix_='sun-fromho