《Sql常见面试题.docx》由会员分享，可在线阅读，更多相关《Sql常见面试题.docx（10页珍藏版）》请在课桌文档上搜索。
1、SQ1.:selecta.titleza.usemame,b.adddatefromtablear(selectma(adddate)adddatefromtablewheretable.title=a.title)b说明，外连接查询(表名1.a表名2：b)SQ1.：selecta.aza.bza.cfbeb.d,b.ffroma1.EFTOUTJOINbONa.a=b.c说明，H程支限提前五分钟提示SQ1.：selectfrom11程支配wheredatediff(mlnute,f起先时间耻tdate()5说明，两张关联表,删除主表中已经在副表中没有的信息SQ1.:deletefrominf
2、owherenotexists(select*fromifobzwhereinfo.infid=infobz.infid)说明：一SQ1.:SE1.ECTA.NUM,A.NAMEzB.UPD.DAT,B.PREV-UPD,DATEFROMIAB1.E1,(SE1.ECTX.NUMzX.UPD-DATezY,UPD.DATEPREV-PD-DATEFROM(SE1.ECTNUM,UPD,DATE,INBOUND,QTSTOCK_ONHANDFROMTAB1.E2WHERETO_CHAR(UPD_DATE；YYYY/MM,)三TO_CHAR(SYSDATE,YYYYMM,)X,(SE1.ECTNU
3、M,UPD.DATE,STOCKeONHANDFROMTAB1.E2WHERETO-CHAR(UPD.DATEz,YYYYMM,)=TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,YYYYMM,)：；,01rYYYYMMDD,)-1,YYYYMM,)where.nm=y.num2oracle90pass3xml40fail4jsp30fail5servlet80pass写出此查询语句没有装ORAC1.E,没试过selectcourseidzCOUgename,score,decodedesccourse-vNameNull?TypeCourseidnumberCOURSENAM
4、EVARCHAR2(10)SCORENUMBERSQ1.select*fromCOUrSJv;CourseidcoursenamescoreIjava702oracle903xml404jsp305servlet80SQ1.selectCOUrSeid,coursename,scorerdecode(sign(score-60)lfai,pass)asmarkfromCOUrSjv;Courseidcoursenamescoremark1java70pass2oracle90pass3xml40fall4jsp30fail5servlet80pass原衣:idproidpronameIlM12
5、F21N22G31B32A瓷询后的表：idprolpro2IMF2NG3BA写出杳询语句解决方案sq求解表a列ala2记录】aIb2x2y2zfllselect能选成以下结果吗？1 ab2 xyz运用pl/sql代码实现，但要求你加合后的长度不能超出OradeVarChar2长度的限制,下面是一个例子createorreplacetypestrings_tableistableofvarchar2(20);Zcreateorreplacefunctionmerge(PVin$tring$_table)returnvarchar2i$ISVarChar2(4000);beginforiinl.p
6、v.countloopIs：,=Is11pv(i);endloop;returnIs;end;/createtablet(idnumber,namevarchar2(10);insertintotva!ues(l,Joan);insertintotvafues(l,Jack,);insertintotvaIues(l,Tm);insertintotvaIues(2,Rose);insertintotvaues(2,Jenny);columnnamesformata80;selectt.id,merge(cast(multiset(selectnamefromtwheret.id三t.id)as
7、strings-table)namesfrom(selectdistinctidfromt)t;droptypeStringsJabIe;dropfunctionmerge;droptablet;用sql：Wellifyouhaveathoreticalmaximum,whichIwouldassumeyouwouldgiventhelegibilityoflistinghundredsofemployeesinthewayyoudescribethenyes.ButtheSQ1.needstousethe1.AGfunctionforeachemployee,henceahundredemp
8、sahundred1.AGs,sokindofbulky.Thisexampleusesamaxof6,andwouldneedmorecutnpastingtodomorethanthat.SQ1.selectdeptno,dname,emps2 from(3 selectd.deptnord.dname,rtrim(e.ename11,4 lead(e.eamezl)over(partitionbyd.depto5 orderbye.ename)6 lead(e.ename,2)over(partitionbyd.deptno7 orderbye.ename)8 eade.enamez3)
9、over(partitionbyd.deptno9 orderbye.ename)10 Ieadgename.4)over(partitionbyd.deptno11 orderbye.ename)(r(12 lead(e.ename,5)over(partitionbyd.deptno13 orderbye.ename)，)emps,14 row_number)over(partitionbyd.deptno15 orderbye.ename)16 fromempe,deptd17 whered.deptno=e.deptno18 )19 where=120/DEPTNODNAMEEMPS1
10、0ACCOUNTINGC1.ARK,KING,MI1.1.ER20RESEARCHADAMS,FORD,JONES,RNEY,SCOTTSMITH30SA1.ESA1.1.ENzB1.AKE,JAMES,MARTIN.TURNER,WARDalso先createfucUonget_a2;createorreplacefunctionget_a2(tmp_alnumber)returnvarchar2isCol_a2VarChar2(4000);beginCol_a2:H;forcurin(selecta2fromuite-awhereal=tmp-al)loopCol_a2=Col_a2|cu
11、r.a2;endloop;returnCO1.a2;endget-a2;selectdistinctalrget-a2(al)fromunite_aIABC2EK53KMN直*xac*，*，*”*：*寥*个3Ql面试鹿去年应聘一个职位未果工间被考了一个看似筒洁的题却.我没有找到好的大案.不知各位大虾有无好的解法?JS为:有两个表,tl,tzTabletl:SE1.1.ERINON_SE1.1.ERABACADBABCBDCACBCDDADBDCTablet2:SE1.1.ERICOUPONBAlA9100B9200C9300D9400A9.S1B9.S20AlO80要求用SE1.ECT语句列出
12、如下结果:一如A的SUM(BAU为BCD的和,B的SUM(BAl)为ACD的和.且用的方法不要增加数据库负担,如用临时表等.NONSE1.1.ERCOUPONISUM(BA1.)A9900B9800C9700D9600A9.520B9.5100C9.5120D9.5120AlOOBlO80C1080D1080关于论坛上那个SQ1.微软面试题问遂，J百个账户在下j100$，某个账户某天如有支出则添加一条新记录，记录其余额。百天后，请输出包天全部账户的余额信息这个问题的难点在于j个用户在某天可能有多条纪录，也可能一条纪录也没有(不包括第一天返回的记录集是一个天oo个用户的纪录集下面是我的思跖：1
13、.创建表并插入测试数据：我们要求username从1-1CREATETAB1.E(db.TA1.E2(username(varchar(50)NOTNU1.1.,用户名outdate)(datetime)NOTNU1.1.z-H期cashfloatjNoTNU1.I-余额)ONPRIMARYdeclareiintseti=lwhileii),2001-10lr100)inserttable2values(convert(varchar(S0),i)f2001-ll-l,r50)seti=i+lendinserttable2values(convert(varchar(S0),(Si)r,200
14、1-10-l,z90)selectafromtable2orderbyoutdatefnvert(int,userame)2 .组合查询语句：a.我们必得返Wl-个从第一天起先到100大的纪录集：如:2001-101(这个日期是随意的)到2002-1-8由于笫一天是随戢一天，所以我们须要下面的SQ1.语句：selecttop100dateadd(d,convert(int,username)-l,min(outdate)asoutdatefromtable2groupbyusernameorderbyconvert(int,username)这里的奥妙在于：convert(intfuserna
15、me)-l(记得我们指定用户名从1-100:-)groupbyUSemame,mln(outdate):第一天就可能每:个用户有多个纪录，返回的结果：outdate*.2001-100100:00:00.0002002-01-0800:00:00.000b返回一个全部用户名的纪录集；selectdistinctusernamefromtable2返回结果:username1010099C返回个100天记录集和100个用户记录集的笛卡尔集合:selectafromselecttop100dateadd(drconvertint,username)-lrmin(outdate)asoutdatef
16、romtable2groupbyusernameorderbycovert(nt,username)a$ACROSSjoin(selectdistinctusernamefromtable2)asBorderbyoutdatezconvert(int,username)返回结果100*100条纪录：outdateusername2001-10-0100:00:00.00012002-01-0800:00:00.000100d返回当前全部用户在数据库的行的纪录：selectoutdate,username,min(cash)ascashfromtable2groupbyOUEate,userna
17、meorderbyoutdatezconvert(int.username)返回纪录:outdateusername2001-100100:00:00.0001cash902002-01-0800:00:00.00010050c将C中返回的笛卜尔桑穗d中返回的纪录做leftjoin:selectC.outdatezC.usernamerD.cashfromselect*fromselecttop100dateadd(drnvert(int,userame)-lrmin(outdate)asoutdatefromtable2groupbyusernameorderbcovert(intruser
18、name)asACROSSjoin(selectdistinctusernamefromtable2)asB)a$Cleftjoin(selectoutdate,userame,min(cash)ascashfromtable2groupbyoutdate,username)asDon(C.username=D.usernameanddateditt(d,C.outdaterD.outdate)=0)orderbyC.outdate,convert(intzC.username)留意,用户在当天假如没有纪录,CaSh字段返回NU1.1.否则CaSh返回柘个川户当天的余额outdateusern
19、amecash2l-100100:00:009001902001-100100:00:00.00021002001-10-0200:00:00.0001902001-100200:00:00.0002NU1.1.-留意这里22-01-0800:00:00.00010050好了,现在我们Ja终要做的就是,假如CaSh为NUl1,我们要返回小于当前纪录日期的笫一个用户余额(由于我们运用OrderbVcash,所以返回top1纪录即可运用min应当也可以)这个余额即为当曲的余微：caseisnull(D.cash,O)when0then(selecttop1cashfromtable2whereta
20、ble2.userame=C.usernameanddatediff(dzC.outd3te,table2.outdate)0orderbytable2.cash)elseD.cashendascashg.最终组合的完用语句就是selectC.outdatezC.username,caseisnull(D.cash,O)when0then(selecttop1cashfromtable2wheretable2.userame=C.usernameanddatediff(dX.outdate,table2.outdate)0orderbytable2.cash)elseDxashendascas
21、hfrom(selectafrom(selecttop100dateadd(d,convert(i11tuserame)l,min(outdate)asoutdarefromtable2groupbyusernameorderbyconvert(intfusername)a$ACROSSjoin(selectdistinctusernamefromtable2)asB)asCleftjoin(selectOUtdate.username.min(CaSh)ascashfromtable2groupbyOutdatefUiername)asDOn(CUsername=D.usernameandd
22、atediff(d,C.outdate,O.outdate)=0)orderbyC.outdate,convert(intzC.username)返回结果:Outdateusernamecash2001400100:00:00.0001902001-10-0100:00:00.00021002002-0108OOrOOW-OOO10050取出SQl表中第31到40的记录（以自动增长ID为主链从数据表中取出第n条到第m条的记录/declaremintdeclarenintdeclare$qlvarchar(800)辑tm=4Osetn=31set$ql=$electtop*+$tr(m-n+l)
23、+*fromdetallwhereautodnotin(selecttop*str(n-l),autoidfromidetail)exec(sql)selecttop10fromtwhereidnotin(selecttop30idfromtorderbyid)ordebyidselecttop10fromtwhereidn(selecttop40IdfromtorderbyId)orderbyiddescx*at*一道面试咫,写Sql语句有表a存储二叉树的节点,要用一条Sql语句直出全部节点及节点所在的层.表aclc2A1/ABBC2ACBDDNE3CEDFFKI4ElDKCN所要犯到的结果如下jdcsAl62C2D3N3E3F4K414有高手指球一下,我只能用psq写出来,请教用一条sql语句的写法
