課件課后練習代碼經典案例

selectselectmin(create_date)fromselectselectunix_host,os_username,create_datefromwherecreate_date=將inda直接換為上述查詢語句，這樣的查詢形式稱為非關聯子查詢。語句的執(zhí)行順序是先執(zhí)行子查詢（只執(zhí)行一遍，若子查詢的返回結果為多條，系統(tǒ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將上述查詢的結果作為hee條件的值,即查詢哪些人的帳務帳號ID沒有落在上述查詢的結果集中，也就是哪些人的帳務帳號ID跟結果集中的所有值都不相等。當子查詢的返回結果中包含有null值，對于ntin（all）來說，l語句不會返回任何結果。因為任何跟null比較，結果都不是u。selectreal_namefromwherenotmender_idfromaccountmender_idisnotnull);顯示unix服務器ip地址,開通日期,os帳號名,結果集中只包含每臺unix服務器上最早開通的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);圖-該問題可以用關聯子查詢的notexists對于主表中的記錄來說不存在（notexists）如下情況：他的帳務帳號ID是某select1select1fromservicewhereo.id該查詢執(zhí)行后返回norowsselected。注意notexists關心是否有返回結果而不是返回結果具selectreal_namefromaccountwherenot(select1fromaccount圖-selectaccount_idselectaccount_idfromselectselect1fromservicewhereo.id=若沒有，主查詢中的notexists是成立的，即該客戶沒有申請登錄業(yè)務selectreal_namefromwhereidnotin(selectaccount_idfromselectreal_namefromaccountwherenotexists(select1fromserviceiwhereo.id=圖-fromaccountfromaccountajoinserviceona.id=columnreal_nameformatcolumnos_usernameformata12columnunix_hostformata16fromaccountajoinserviceona.id=圖-fromfromaccountajoins