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

TTS6.0（ORACLE達內(nèi)ITORACLESQL列出客戶以及開通的登錄業(yè)務的數(shù)列出客戶以及他的推薦哪些客戶是推薦列出客戶以及他的推薦列出客戶以及所開通的登錄業(yè)務的信息(沒有申請登錄業(yè)務的客戶列出客戶以及開通的登錄業(yè)務的數(shù)哪些客戶不是推薦哪些UNIX服務器上沒有os帳號顯示客戶的顯示客戶huangrong的顯示各個段的客戶數(shù)（沒有客戶的段不用出現(xiàn)在結(jié)果集中顯示各 段的客戶數(shù)（沒有客戶的段的客戶數(shù)為當月 時長為20小時，單位費用漲5分，為40小時漲3分，其他不變（列出客戶以及他的推薦sun280和sun-server上 登錄業(yè)務使用了哪些相同的資費標哪臺UNIX服務器上沒有開通登錄業(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表，但包含兩列：客戶和推薦人的，所謂推薦人是指他的帳務帳號是別人的mendi,假設huanng的帳務帳號ID是1，luwuhuang的帳務帳號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，因此找不到對應的帳務帳號id跟他匹配。外連接的一個應用場景就是讓匹配不上的記錄也出現(xiàn)在結(jié)果集中。選擇accounta1表做fromaccounta1leftjoinaccountmender_id=selecta1.real_namefromaccounta1leftjoinaccountmender_id=列出客戶以及所開通的登錄業(yè)務的信息(沒有申請登錄業(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實現(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