还剩7页未读,继续阅读
文本内容:
绸写S SQL查询奈实现分数排名.如果两个分数相同,则两个分规3电Rank相同.清注意.平分后的下一个W次应该是下一个逐渎的暨值换句话说,名次之间不应该有“间隔”I Id|Score||1|
3.50I|2|
3.65I|3|
4.00I|4|
3.85I|5|
4.00I|6|
3.65I例如,根症上述给定的Scores表,你的查词应该退回按分裁从高到俗冽|Score|Rank|00|100|1385|2365|3365|3350|4select o.score.p.Rank fromscores o,select score,@row:=@row+1as Rankfrom select@row:=0r,select e.score fromscores egroup by e.score order bye.score descb Pwhere o.score=p.score order by o.score desc思路首先进行分数分组排序,算出成绩高低;接着引用变量存储行号,此时成绩是分组排序好了,所以行号就是排名,把这两个查询合并为P;接着就是让每一个成绩都与P表比较,取O的成绩,取P表排名,这样就有多个同分同排名了;最后按分数在降序就ok总结下sql细节
1.in+中间有无空格都可以,括号中的子查询不用完后不要加别名
2.from表A,子查询AS B,如果是from后加复杂的子查询一定要加别名
3.update表A set字段二’’where…
4.insert into表A Values字段一,…这种是每个字段都要加,如果只是插入某个字段,则INSERT INTOtable.name列1,列2,...VALUES值1,值2,.…VALUES前有空格
5.delete fromA where…
6.创建视图的写法CREATE view视图名as后面加查询语句真题难点解析
2013、2015年都不算难,主要是
16、
17、18年难度逐渐递增
一、2016年真题
1.统计每个顾客在2015年度的累计购买金额的视图CREATE viewCustSum asselect c.cid,c.cname,t.sum_dols total_dols from c,select cid.sumdols sum_dols from o whereyearo.orddate二yeardate_subnow,interval1year group by cidt where c.cid=t.cid思路先找出2015年的订单,然后按客户id分组,就可以算出最大值,然后作为一个表跟客户表关联,去客户名字就可以了时间的相关总结daydate、monthdate、yeardate分别可以把去具体日期中的天/月/年选取时间各部分SELECT now,datenow;--日期SELECT now,timenow;--时间SELECT nowQ,yearnowO;--年SELECT now,quarternow;--季度SELECT now,monthnow;-■月SELECT now,weeknow;--周SELECT now,daynow;--日SELECT now,hournow;--小时SELECT now,minutenow;--分钟SELECT now,secondnow;--秒SELECT now,microsecondnow;--微秒date_adddate,interval数字类型表示对date加多少天/月/周,具体根据类型来定等date_subdate,interval数字类型:用法和加相似,只不过是对date减type的值如下获得当前日期now2008-08-0822:20:46时间转换date_formatdate,format datediffdatel,date2:日期相减,是datel-date2只能精确到天数如:select datediffC2008-08-08z2008-08-01;-7TIMESTAMPDIFF函数,可以精确到天DAY、小时HOUR,分钟MINUTE和秒SECOND,使用起来比datediff函数更加灵活--相差1天select TIMESTAMPDIFFDAYZ2018-03-2023:59:00,*2015-03-2200:00:00;-■相差49小时select TIMESTAMPDIFFHOUR,2018-03-2009:00:00,2018-03-2210:00:00;■■相差2940分钟select TIMESTAMPDIFFMINUTE,2018-03-2009:00:00;*2018-03-2210:00:00;■■相差176400秒select TIMESTAMPDIFFSECONDZ2018-03-2009:00:00,2018-03-2210:00:00*;
2.查询只向南京市的顾客销售过商品的供应商的编号select DISTINCTaid fromozc where c.cid=o.cid and c.city=南京and o.aid not in select o
2.aid fromo o2,c where o
2.cid=c.cid andc.cityo南京思路通过内层查询先找出给不是南京市的顾客供应过商品的供应商A,外层查询向南京市的顾客供应过商品的供应商B,但是B中有包含只向南京市供应的,还包含不止向南京市供应的,所以再通过not inA,就可以把不止向南京市供应的供应商去掉总结碰到只查询C一般可以转为not inoC的这种情况
3.统计查询每一个供应商的累计销售金额,结果返回供应商编号及其累计销售金额,并按照累计销售金额从高到低降序输出查询结果select a.aid,sumo.dols from a LEFTjoin oon a.aid=o.aid group by a.aid ORDER BY sumo.dols desc这个主要是提一下排序是可以用sum
4.查询在2018年2月份到8月份累计购买金额超过500元的顾客的编号和名称注意年的用法select c.cid,c.cname fromczo wherec.cid=o.cid andyearo.orddate=2018and montho.orddate BETWEEN2and8group by o.cid HAVINGsumdols280主要是between aand b的用法
5.查询每一个顾客的最后一份订单,结果返回顾客的编号、订单编号、订单日期select o.cidzo.ordno,o.orddate fromozselect cidzmaxorddateorddate fromo group by cidt where o.orddate=t.orddate and o.cid=t.cid分组查最大的思路先按正常分组取最大值,然后把结果令为一个子表,做关联查询,让值=分组最大值,让id=分组id
6.查询向所有顾客都销售过商品的供应商的编号select aid fromawhere not EXISTS select*fromc where notEXISTS select*fromo whereo.aid=a.aid ando.cid=c.cid除法思路查询一个表包含另一个表的信息,即除法的思路,那我们皇不犹豫转为两个not exist,一般都是3个表,最外层是要查的,中间的辅助的,最内是能关联外层和中间层的
二、2017年真题
1.求员工工资中位数难点select avgsalary from select eno,salary fromselect emp.eno,@index:=@index+l asmyindex,emp.salary from emp,select@index:=0as initvarorderbyemp.salary t where floor@index/2+l=myindex or ceil@index/2=myindex x思路通过变量来设置行号,然后向上和两下取整取中间数据1select@index:=0as initvar这个是初始化变量,并设置为0,而且要作为一个子表,而且是在from后面所以要起别名,不起别名语句报错2@index:=@index+l asmyindex这个就是设置行号,注意变量的设置都是:=的形式,而且是要起别名,因为后面要用到3排序完后变量@index就为行数比如10行,那最后排序完就等于104以上作为一个子表,然后从这个子表中取中间的数据,取法就是依靠floor向下取整和ceil向上取整比如行数为10,中位数应该是5和6的平均值,也就是我们要取第5行和低6行的数据,floor10/2+l=floor6=6取myindex为6的行,ceil10/2=5取myindex为5的行如果行数是5,要取第3行,同理floor5/2+l=floor
3.5=3„ceil5/2=ceil
2.5=3,所以只会取到1行,取到第3行5然后再把取到的行作为一个子表,最后取平均值即可
2.求各部门去除最高工资和最低工资的员工外,每个部门所有员工的平均工资偏难select emp.dno,avgemp.salary from emp,select dnozminemp.salary aslowzmaxemp.salary ashigh from emp GROUP BY dnoe whereemp.dno=e.dno andemp.salarye.low andemp.salaryoe.high GROUP BY dno思路首先按部门分组,取各部门号、各部门最低和最高工资,然后作为子表做关联查询,并且工资不能等于最高和最低.这样取到的数据就是去除最高和最低的了,别忘了部门号也要做关联
3.求各部门最大■最小入职时间的转换,多少年/多少月select TRUNCATETIMESTAMPDIFFDAY,minhire,maxhire/365,2as yearsl,TRUNCATETIMESTAMPDIFFDAY,minhire/maxhire/30z2as monthslfromempGROUP BY dno思路因为如果用TIMESTAMPDIFF在转为年的话,会舍去小数点,所以我们先要化为天,在转为年和月,但是不是很准确,但至少比转接转为年精确主要介绍TRUNCATE
100.223344,2=
100.22可以保留小数点后几位还有一个函数也需要记一下Round,是做四舍五入21#按时间排序,求最大-最小入职时间的转换,换成年和换成月,不精确;第二种做法相对I22select dno,23TIMESTAMPDIFFYEAR,minhire,maxhire as years,24TIMESTAMPDIFFHONTH,minhire,maxhire as months,25TIMESTAMPDIFFDAY,minhire,maxhire/365as yearsl,26TIMESTAMPDIFFDAY,minhire,maxhire/30as monthsl,27TRUNCATETIMESTAMPDIFFDAY,minhire,maxhire/365,2asyears2,28TRUNCATECTIMESTAMPDIFFCDAY^minChire^maxChire/30,2asmonthsfromempGROUP29BY dno信息Result1剖析状态dno yearsmonths yearslmonthsl years2months
312282.
33728.
43332.
3328.
432080.
66588.
10.
668.1
三、2018年真题
1.查询同时工作于硬件和软件部门的每个员工的名字和年龄除法,可以转为not exists select e.ename,e.age fromemp2018e where notEXISTS select*from dept2018d whered.dname=硬件”or d.dname=软件and notEXISTS select*from works2018w wheree.eid=w.eid andw.did=d.did思路只要看到有除的关系,都可以转为not exist.只不过要注意OR条件要括起来,不然不
2.找出那些工资超过他所在部门预算20%的员工select distincte.eid fromemp2018ezdept2018d,works2018w wheree.eid=w.eid andw.did=d.did ande.salary d.budget*
0.2思路做关联就可以了
3.找出预算最高的前2个部门管理者的管理员id有难有易select managerid from dept2018ORDER BY budget desclimit2注意limit2只能取两条,如果有多个相同的最高,这样做不行select DISTINCTdl.managerid from dept2018dl,select d.budget,@row:=@row+l asmyrow fromselect*fromdept2018GROUP BYbudget ORDERBYbudgetdesc deselect@row:=0as rowlx wheredl.budget=x.budget andx.myrow=l orx.myrow=2分析这种方式可以取相同最高的前2位思路我们按预算分组,然后把分组结果作为一个表,在关联变量表,变量表主要做行号,因为我们想去最大、和第二大的预算值,切记不能再分组的时候就加行号,这样结果不对然后再令为一个表,和原表关联取预算相同,并且myrow等于1或2,就可以取到多人预算相同且都是最高或第二高的情况
4.一个管理员管理多个部门,那么他控制的部门预算就是这些部门的预算的总和,找出总和大于5000的管理者id selectmanageridfromdept2018GROUP BYmanagerid HAVINGsumbudget5000分析这个考点应该是having的用法
5.计算2017年中有多少个星期个数难点,并给出设计的表结构select DATE_FORMATDATE_ADDCAST2017-01-01as date,INTERVAL readrinfo.Rdr_Id-l DAY/%W day,count*from readrinfowhere readrinfo.Rdr_Id=DATEDIFF cast2018-01-01as date,cast2017-01-Olas dategroup byDATE_FORMATDATE_ADDCAST‘2017-01-01,as date,INTERVAL readrinfo.Rdr_Id-l DAY/%W#group byday可以替换groupby后面那一大串,本质就是用星期分组,而且我们也给这个字段起别名,如果没起的话,那就不能替换思路表readrinfo只有一个Rdr_Id字段,取值为1-357,或者大于356都可以,如果只计算一年的话356够了,因为一年最多356天,注意id・l因为date_adddate,interval数字type第一个参数是日期型,所以要用case函数做转化,Cast字段名as转换的类型,可以转换的类型见下图我们用2017-01-01分别加表readrinfo中每一个id值・1,然后用date_format可以转为对应的星期几英文,接下来我们需要分组,分组前计算下一年的天数,用了datediff算了2018-01-01到2017-01-01的天数,这样可以确定之前date_add应该加多少行的id值,然后根据算出的结果分组就可以了,如果我们在取的时候起了别名,比如day,那groupbyday是可以的,就没必要重复写后面那一串/I MU*9*77Rdr IdCHAR[N]字符型DATE日段里DATETIME日期和时间型34DECIMAL float型5SIGNED int6TIME时间型
76.查询尚未完成的状态订单假设除了3都是未完成,列出oid,cname,以及最后的状态status和设置statusdate较难select t.oidzc.cnamezt.status,t.statusdate from orders od,customers c,select os.*from orderstatusos/select oidzmaxstatusdatestatusdate from orderstatus groupby oidts whereos.oid=ts.oid andos.statusdate=ts.statusdate andos.status3t whereod.oid=t.oid andod.cid=c.cid分析首先按oid分组取最大的订单时间,然后作为一个子表关联订单表,取状态不是3的,这样就取到关键的数据,最后在关联一次取相应的信息就可以了
7.计算每个员工的deptno、ename、sal以及与同一个部门员工之间的sal的差值,这个差值是在当前员工与同部门内紧随其后的员工之间计算,对聘用的而员工这个是N/S难题select c.empno,c.ename,c.deptno,c.hiredate,c.curr assalary,c.curr-c.pre asN/S fromselect b.empnozb.ename,b.deptno,b.hiredate,b.curr;ifb.rrb.rri;Ozb.pre aspre fromSELECT e.empno ze.ename,e.deptno,e.hiredate,e.sal AScun;@temp ASpre,@temp:=e.salz@templ asrr;@templ:=e.deptno asrrr FROMemp2018_2e,SELECT@temp:=0「,SELECT@templ:=0rl ORDERBY deptno,hiredate descb cORDERBYc.deptnoz c.hiredate后面补充.这个题主要针对变量的使用,而且对变量的位置有要求
四、2012年期末试卷的好题选择
1.查询每个部门中工资最高的职工,返回部门编号和该职工工号select e.dno,e.eno frome,select dno,maxsalary assalaryfrome GROUP BYdnoel wheree.dno=el.dno ande.salary=el.salary分析查最大思路都是先分组取最大和关联的id,然后把查询的结果令为一个表,再跟关联id的那个表进行关联,取id和工资都相等的,这样可以取到多个相同且都是最大的人,不要单纯想只是一个人
2.检索只参加过一个项目的职工的姓名selecte.ename frome wheree.eno in select eno from wGROUP BYw.eno HAVINGcount*=l思路分组后having count*=1是这个题的关键
3.检索只参加过项目P1的职工姓名select enamefrome,w wheree.eno=w.eno ande.eno notinselectenofromw wherew.pno1思路首先取没有参加过Pl项目的员工,但有可能有两种情况只参加了P1以及都没参加的情况,然后我们做关联e.eno=w.eno,这样就去掉了都没参加的项目的员工
五、作业题精选来源于text_
20140324.ch3[l],网上找的刘嘉老师的部分作业题查询供应商每一份订单的销售金额都小于50的供应商select a.aname fromagents a,select aidfrom ordersGROUPBYaid HAVINGmaxdollars50al wherea.aid=al.aid思路选这个题是因为出现小于的情况,每一份小于50,那就意味最大要小于
502.查询满足下述条件的订单:销售金额高于该商品所有订单的平均销售金额结果返回商品编号,订单编号和销售金额,并按照商品编号的升序和销售金额的降序输出查询结果select o.pidzo.ordnozo.dollars from orders oselect pid,avgdollars asavg_dols from orders groupby pidol whereo.pid=ol.pid ando.dollarsol.avg_dols orderbyo.pid asczo.dollars desc分析这个题不难,主要是orderby的多个排序规则,要注意顺序
3.找出购买的每一类商品的平均订单数量都大于300的客户编号注意:、、都超过select T.cid fromselect cid,pid,AVGqty asq_avg fromorders groupby cidzpidT GROUPBY T.cid HAVINGminT.q_avg=300分析这个题和1题有异曲同工之妙,不过有点灵活,需要按照客户编号和商品编号分组,然后求平均值,然后再按客户编号分组找最小的平均值大于300的,这样就是题目所要求的都超过
4.查询仅通过1和2经销商订购过商品的客户编号select DISTINCTol.cid fromorders ol where ol.cid notinselect o
2.cid fromorders o2whereo
2.aid olando
2.aid2分析这个题是表自关联、先从订单表找出不是1和2的客户编号,然后再从订单表找不在其结果的客户编号思路就是看到仅,一般都是用notin做双重否定
5.只通过2号经销商订购过的商品的客户编号,并给出客户的订购总金额select cidzsumdollarsfromorderswhere cidnotinselect cidfromorderswhere cid2GROUPBYcid分析同样出现了“只,用not inz只不过是要对结果分组统计
6.检索居住在海口和北京的所有客户订购过同一商品的经销商编号select o.aidfromorders owhere not existsselect*from customerscwherec.city=海口or c・city=北京and notEXISTS select*fromordersolwhereol.aid=o.aid andol.cid=c.cid andol.pid=o.pid分析典型的除法,只不过要注意or的条件,要括号起来,不然结果不对
7.查询同时购买nike品牌2019年新上市的最贵的外套和裤子的顾客姓名select cl.cname fromcustomer clwherenotEXISTSselect*from clothes c2/select c.cid,maxc.price fromclothes cwherec.brand=nike andc.launchYear=2019andc.type=2orc.type=3groupbyc.type c3wherec
2.cid=c
3.cid andnot EXISTSselect*fromorder owherecl.cuid=o.cuid ando.cid=c
2.cid分析复杂的除法情况,两个notexist少不了,主要是中间那层需要找出最贵的外套和裤子需要做关联.找出cid.这里的用法主要是c.cid,maxc.price,这个在查询时cid就是对应的最大价格那个
8.查询
2019.
03.06号这天销量前三的服装名称、总销量、以及品牌SELECT c.name zt.totalQuantityzc.brand fromclothesc,selecto.cidz sumo.quantity astotalQuantity fromorderowhere DATE_FORMATo.orderTime,%Y・%m.%d=,2019-03-06,GROUPBYo.cid ORDERBY sumo.quantity descLIMIT3twherec.cid=t.cid分析:主要是看下DATE.FORMAT的用法,其中%Y・%m-%d这个一定要记住、而且后面的时间一定要加气题的思路就是分组然后按销量降序取前三个,然后关联取相应的值。