课件课后练习代码经典案例

selectselectmin(create_date)fromselectselectunix_host,os_username,create_datefromwherecreate_date=将inda直接换为上述查询语句，这样的查询形式称为非关联子查询。语句的执行顺序是先执行子查询（只执行一遍，若子查询的返回结果为多条，系统会自动去重）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该查询执行后返回norowsselected。注意notexists关心是否有返回结果而不是返回结果具selectreal_namefromaccountwherenot(select1fromaccount图-selectaccount_idselectaccount_idfromselectselect1fromservicewhereo.id=若没有，主查询中的notexists是成立的，即该客户没有申请登录业务selectreal_namefromwhereidnotin(selectaccount_idfromselectreal_namefromaccountwherenotexists(select1fromserviceiwhereo.id=图-fromaccountfromaccountajoinserviceona.id=columnreal_nameformatcolumnos_usernameformata12columnunix_hostformata16fromaccountajoinserviceona.id=图-fromfromaccountajoins