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

selectselectmin(create_date)fromselectselectunix_host,os_username,create_datefromwherecreate_date=將inda直接換為上述查詢語(yǔ)句，這樣的查詢形式稱為非關(guān)聯(lián)子查詢。語(yǔ)句的執(zhí)行順序是先執(zhí)行子查詢（只執(zhí)行一遍，若子查詢的返回結(jié)果為多條，系統(tǒng)會(huì)自動(dòng)去重）selectunix_host,os_username,create_datefromwherecreate_date=(selectmin(create_date)from圖-selectcreate_datefromwhereos_username='huangr';andunix_host='192.168.0.26'selectunix_host,create_date,os_usernamefromwherecreate_date(selectcreate_datefromwhereos_username='huangr'andunix_host='192.168.0.26');圖-圖-selectselectcreate_datefromwhereos_username=selectunix_host,create_date,os_usernamefromwhere(selectcreate_datefromservicewhereos_username='huangr');selectunix_host,create_date,os_usernamefromwhere(selectmax(create_date)fromservicewhereos_username='huangr');selectunix_host,create_date,os_usernamefromwhere(selectcreate_datefromservicewhereos_username='huangr');selectunix_host,create_date,os_usernamefromwhere(selectmin(create_date)fromservicewhereos_username='huangr');圖-mender_idfrommender_idfromselectreal_namefromwhereidinmender_idfrom圖-mender_idfrommender_idfrom將上述查詢的結(jié)果作為hee條件的值,即查詢哪些人的帳務(wù)帳號(hào)ID沒有落在上述查詢的結(jié)果集中，也就是哪些人的帳務(wù)帳號(hào)ID跟結(jié)果集中的所有值都不相等。當(dāng)子查詢的返回結(jié)果中包含有null值，對(duì)于ntin（all）來說，l語(yǔ)句不會(huì)返回任何結(jié)果。因?yàn)槿魏胃鷑ull比較，結(jié)果都不是u。selectreal_namefromwherenotmender_idfromaccountmender_idisnotnull);顯示unix服務(wù)器ip地址,開通日期,os帳號(hào)名,結(jié)果集中只包含每臺(tái)unix服務(wù)器上最早開通的os圖-selectunix_host,min(create_date)fromgroupbyselectunix_host,os_username,create_datefromwhere(unix_host,create_date)(selectunix_host,min(create_date)fromservicegroupbyunix_host);圖-selectto_char(min(create_date)+9,'yyyymmdd')fromservicegroupbyselectunix_host,os_username,create_datefromwhere(unix_host,to_char(create_date,'yyyymmdd'))in(selectunix_host,to_char(min(create_date)+fromgroupby圖-selectselectround(sysdate-create_date)selectavg(round(sysdate-create_date))fromservicewherei.unix_host=selectunix_hostos_usernameround(sysdate-create_date)open_agefromserviceowhereround(sysdate-create_date)(selectavg(round(sysdate-fromservicewhereo.unix_host=圖-select1fromservicewhereo.idselectreal_namefromaccountwhereexistsselect1fromaccountwhereo.id圖-selectaccount_idselectaccount_idfromselectselect1fromservicewhereo.id=selectreal_namefromwhereidin(selectaccount_idfromselectreal_namefromaccountwhere(select1fromserviceiwhereo.id=i.account_id);圖-該問題可以用關(guān)聯(lián)子查詢的notexists對(duì)于主表中的記錄來說不存在（notexists）如下情況：他的帳務(wù)帳號(hào)ID是某select1select1fromservicewhereo.id該查詢執(zhí)行后返回norowsselected。注意notexists關(guān)心是否有返回結(jié)果而不是返回結(jié)果具selectreal_namefromaccountwherenot(select1fromaccount圖-selectaccount_idselectaccount_idfromselectselect1fromservicewhereo.id=若沒有，主查詢中的notexists是成立的，即該客戶沒有申請(qǐng)登錄業(yè)務(wù)selectreal_namefromwhereidnotin(selectaccount_idfromselectreal_namefromaccountwherenotexists(select1fromserviceiwhereo.id=圖-fromaccountfromaccountajoinserviceona.id=columnreal_nameformatcolumnos_usernameformata12columnunix_hostformata16fromaccountajoinserviceona.id=圖-fromfromaccountajoins