《个人整理版真题数据库SQL部分答案.docx》由会员分享，可在线阅读，更多相关《个人整理版真题数据库SQL部分答案.docx（9页珍藏版）》请在金锄头文库上搜索。
1、绸写S SQL查询奈实现分数排名.如果两个分数相同，则两个分规3电(Rank)相同.清注意.平分后 的下一个W次应该是下一个逐渎的暨值。换句话说，名次之间不应该有“间隔”。I I II III Id | Score | 1 | 3.50| 2 | 3.65| 3|4.00| 4|3.85| 5 | 4.00| 6 | 3.65例如，根症上述给定的Scores表，你的查词应该退回(按分裁从高到俗冽)：| Score | Rank |00 | 100 | 133333333333385 | 265 | 365 | 350 | 4select o.score.p.Rank from scores o
2、,(select score,(row:=row +1) as Rank from (select row:=0) r,(select e.score from scores e group by e.score order by e.score desc) b )Pwhere o.score = p.scoreorder by o.score desc思路：首先进行分数分组排序，算出成绩高低；接着引用变量存储行号，此时成绩是分组 排序好了，所以行号就是排名，把这两个查询合并为P ；接着就是让每一个成绩都与P表比 较，取O的成绩，取P表排名，这样就有多个同分同排名了；最后按分数在降序就ok总结
3、下sql细节1. in+()中间有无空格都可以，括号中的子查询不用完后不要加别名from表A,(子查询)AS B ,如果是from后加复杂的子查询一定要加别名update 表 A set 字段二where insert into表A Values(字段一，)这种是每个字段都要加，如果只是插入某个字段,则 INSERT INTO table.name (列 1,列 2,.) VALUES (值 1,值 2，.)VALUES 前有空格delete from A where 创建视图的写法CREATE view视图名as后面加查询语句真题难点解析2013、2015年都不算难，主要是16、17、18年
4、难度逐渐递增一、2016年真题统计每个顾客在2015年度的累计购买金额的视图CREATE view CustSum asselect c.cid,ame,t.sum_dols total_dols from c,(select cid.sum(dols) sum_dols from o where year(o.orddate)二year(date_sub(now(),interval 1 year) group by cid) twhere c.cid = t.cid思路：先找出2015年的订单，然后按客户id分组，就可以算出最大值，然后作为一个表跟 客户表关联，去客户名字就可以了时间的相关
5、总结：day(date)、month(date)、year(date)分别可以把去具体日期中的天/月/年选取时间各部分SELECT now(),date(now();-日期SELECT now(),time(now();-时间SELECT nowQ,year(nowO)；-年SELECT now(),quarter(now();-季度SELECT now(),month(now(); - 月SELECT now(),week(now();-周SELECT now(),day(now();-日SELECT now(),hour(now();-小时SELECT now(),minute(now()
6、;-分钟SELECT now(),second(now();-秒SELECT now(),microsecond(now();-微秒date_add(date, interval数字类型)：表示对date加多少天/月/周，具体根据类型来定等 date_sub(date, interval数字 类型):用法和加相似，只不过是对date减 type的值如下获得当前日期：now() 2008-08-08 22:20:46时间转换：date_format(date,format)datediff(datel,date2):日期相减，是datel-date2只能精确到天数如:select datedif
7、fC2008-08-08z 2008-08-01); - 7TIMESTAMPDIFF 函数,可以精确到天(DAY)、小时(HOUR),分钟(MINUTE)和秒(SECOND), 使用起来比datediff函数更加灵活-相差1天select TIMESTAMPDIFF(DAYZ 2018-03-20 23:59:00, *2015-03-22 00:00:00);-相差49小时select TIMESTAMPDIFF(HOUR, 2018-03-20 09:00:00, 2018-03-22 10:00:00); 相差2940分钟select TIMESTAMPDIFF(MINUTE, 201
8、8-03-20 09:00:00； *2018-03-22 10:00:00); 相差176400秒select TIMESTAMPDIFF(SECONDZ 2018-03-20 09:00:00, 2018-03-22 10:00:00*);查询只向南京市的顾客销售过商品的供应商的编号select DISTINCT aid from ozc where c.cid = o. cid and c.city =南京and o.aid not in (select o2.aid from o o2,c where o2.cid = c.cid and c.cityo南京)思路：通过内层查询先找出给
9、不是南京市的顾客供应过商品的供应商A,外层查询向南京市的顾 客供应过商品的供应商B,但是B中有包含只向南京市供应的，还包含不止向南京市供应的， 所以再通过not in A,就可以把不止向南京市供应的供应商去掉总结：碰到只查询C。一般可以转为not in(oC)的这种情况统计查询每一个供应商的累计销售金额，结果返回供应商编号及其累计销售金额，并按照 累计销售金额从高到低降序输出查询结果select a.aid,sum(o.dols) from a LEFT join o on a.aid = o.aid group by a.aid ORDER BY sum(o.dols) desc这个主要是提
10、一下排序是可以用sum ()1. 查询在2018年2月份到8月份累计购买金额超过500元的顾客的编号和名称 注意年 的用法select c.cid,ame from czo where c.cid = o.cid and year(o.orddate)=2018 and month(o.orddate) BETWEEN 2 and 8 group by o.cid HAVING sum(dols)280 主要是between a and b的用法查询每一个顾客的最后一份订单，结果返回顾客的编号、订单编号、订单日期select o.cidzo.ordno,o.orddate from oz(se
11、lect cidzmax(orddate) orddate from o group by cid ) t where o.orddate = t.orddate and o.cid = t.cid分组查最大的思路：先按正常分组取最大值，然后把结果令为一个子表，做关联查询，让值=分 组最大值，让id=分组id查询向所有顾客都销售过商品的供应商的编号select aid from a where not EXISTS(select * from c where not EXISTS(select * from o where o.aid= a.aid and o.cid = c.cid)除法思路
12、查询一个表包含另一个表的信息，即除法的思路，那我们皇不犹豫转为两个not exist, 一般都是3个表，最外层是要查的，中间的辅助的，最内是能关联外层和中间层的二、2017年真题1 .求员工工资中位数(难点)select avg(salary) from(select eno,salary from(select emp.eno, index:=index+l as myindex, emp.salary from emp,(select index:=0) as initvar order by emp.salary) twhere floor(index/2+l)=myindex or c
13、eil(index/2) = myindex) x思路：通过变量来设置行号，然后向上和两下取整取中间数据(select index:=0) as initvar这个是初始化变量，并设置为0,而且要作为一个子表， 而且是在from后面所以要起别名，不起别名语句报错index: = index+l as myindex这个就是设置行号，注意变量的设置都是:=的形式， 而且是要起别名，因为后面要用到1) 排序完后变量index就为行数(比如10行，那最后排序完就等于10)以上作为一个子表，然后从这个子表中取中间的数据，取法就是依靠floor向下取整和ceil向上取整。比如行数为10,中位数应该是5和
14、6的平均值，也就是我们要取第5行和低6 行的数据,floor( 10/2+l)=floor(6)=6 取 myindex 为 6 的行，ceil(10/2) = 5 取 myindex为5的行。如果行数是5,要取第3行，同理floor(5/2+l)=floor(3.5)=3 ceil(5/2)=ceil(2.5)=3,所以只会取到1行，取到第3行然后再把取到的行作为一个子表，最后取平均值即可求各部门去除最高工资和最低工资的员工外，每个部门所有员工的平均工资(偏难)select emp.dno,avg(emp.salary) from emp, (select dnozmin(emp.salar
15、y) as lowzmax(emp.salary) as high from emp GROUP BY dno) ewhere emp.dno = e.dno and (emp.salarye.low and emp.salaryoe.high) GROUP BY dno思路：首先按部门分组，取各部门号、各部门最低和最高工资，然后作为子表做关联查询，并且 工资不能等于最高和最低.这样取到的数据就是去除最高和最低的了，别忘了部门号也要做关联求各部门最大最小入职时间的转换，多少年/多少月select TRUNCATE(TIMESTAMPDIFF(DAY,min(hire),max(hire) /365,2) as yearsl,TRUNCATE(TIMESTAMPDIFF(DAY,min(hire)/max(hire)/30z2) as monthsl from emp GROUP BY dno思路：因为如果用TIMESTAMPDIFF在转为年的话，会舍去小数点，所以我们先要化为天，在 转为年和月，但是不是很准确，但至少比转接转为年精确主要介绍TRUNCATE (100.223344,2) =100.22可以保留小数点后几位还有一个函数也需要记一下Round。,是做四舍五入212223242526272829#按时间排序，求最大-最小入职时间的转换，换成年和换
