1.构造数据插入方案表t_project_finish表
a)将addtime更新为当前时间的前一天
首先想到的是addtime=addtime-1,然后就开始验证这个想法。
插入一行数据,包括主键和addtime列.
insert into t_project_finish (lotid,pid,addtime) values (1,2,to_date('20130901 12:09:47','YYMMDD HH24:MI:SS'));
然后更改这一行。
update t_project_finish set addtime = addtime-1 where lotid=1 and pid=2
提交事务。查询验证结果:
这说明oracle会自动计算date类型值的加减。
b)比较allmoney和facemoney字段,将较小的一个更新到partlyreturnmoney中
这一题使人联想到?:三元运算符,google之,原来oracle中也有类似功能的函数decode。
DECODE(value,if1,then1,if2,then2,if3,then3,...,else)
表示:如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。
构造decode(sign(变量1-变量2),-1,变量1,变量2),如果变量1小于变量2,那么sign(变量1-变量2)=-1,所以decode返回变量1。
故所求SQL语句如下:
update t_project_finish set partlyreturnmoney =decode(sign(allmoney-factmoney),-1,allmoney,factmoney);
后来发现least函数,那么答案就更简单了。
update t_project_finish set partlyreturnmoney=least(allmoney,factmoney);
2.取出认购表t_prouser_finish中lotid为46、按addtime升序排列后的第6到10条记录
此题初看很简单,其实很繁琐。
select * from ( select A.*, rownum rn from (select * from t_prouser_finish where lotid = 46 order by addtime) A where rownum <= 10 )) where rn >= 6;
这条sql语句有三层嵌套,最内层查询t_prouser_finish中lotid为46、按addtime升序排列后的所有记录,次内层查询了前十条记录,并加了一列行号RN,最外层查询行号大于等于六的记录,从而达到最终目的。
本题有两个陷阱:
- 是排列后选择6-10行,而不是选择6-10行后排列。
- 关键字rownum虽然表示行号,但是只能用其记录某行之前的数据。(不明白为什么)所以,要查询某行之后的数据目前只能增加一列专门记录行号,再用where子句进行筛选。
3.查询lotid为9,pid为161935 的方案的所有认购信息 返回字段:认购表rgid,username,paymoney,以及方案的suctime
典型的左连查询。
select A.rgid,A.username,A.paymoney,B.suctime from (select * from t_prouser_finish where lotid=9 and pid=161935) A left join t_project_finish B on A.lotid=B.lotid and A.pid=B.pid;
这个语句是对的,但是条件很冗余,应该可以用其他关键字获得更高效更简洁的语句。
【改】取消了一层嵌套,原因在于on where的配合使用是我开始不知道的,而两者的运行效率是一样的,显然后者更好。
select A.rgid,A.username,A.paymoney,B.suctime from t_prouser_finish A left join t_project_finish B on A.lotid=B.lotid and A.pid=B.pid where A.lotid=9 and A.pid=161935;
4.根据addtime,按天统计认购表的paymoney总和 返回:日期,paymoney总和,按日期升序排列
这个题目加深了我对group以及嵌套查询的理解。
select day,sum(paymoney) from (select to_char(addtime,'YY-MM-DD') as day,paymoney from t_prouser_finish) group by day order by day;
【改】同样取消了一层嵌套,因为group by可以接列名及其计算表达式,但是不能跟别名,之所以开始使用嵌套查询就是因为我使用group by day报错。
select to_char(addtime,'YY-MM-DD') as day,sum(paymoney) from t_prouser_finish group by to_char(addtime,'YY-MM-DD') order by day;
5.查出方案表的每个方案的allmoney与该方案认购表所有paymoney总和不相等的记录 返回 lotid,pid
select A.lotid,A.pid from t_project_finish A, (select lotid,pid,sum(paymoney) as allmoney from t_prouser_finish group by lotid,pid) B where A.lotid = B.lotid and A.pid = B.pid and A.allmoney != B.allmoney order by lotid;
效率很低,暂时只想到这个办法了。
【改】多表查询改连接查询,一般情况下连接查询性能优于多表。
select A.lotid,A.pid from t_project_finish A join (select lotid,pid,sum(paymoney) as allmoney from t_prouser_finish group by lotid,pid) B on A.lotid = B.lotid and A.pid = B.pid and A.allmoney != B.allmoney order by lotid;
通过这几道SQL的基础题,自觉SQL基础薄弱,不过学习的过程很快乐,希望能够越学越好。