一、已知程序和数据
create table test1
(id int primary key,
name varchar(20),
money int); insert into test1 values(1,'Tom',1000);
insert into test1 values(2,'Mary',2000);
insert into test1 values(3,'Mike',3000);
insert into test1 values(4,'Jeff',4000);
要求根据下图写出相应的sql语句。
分析：该题使用自连接和左外连接可以解决
select t1.id id,t1.name name,t1.money money,t2.money money1 from test1 t1,test1 t2 where t1.id-1=t2.id(+) order by id
或者
select id,name,money,(select money from test1 where id=t.id-1 ) money1
from test1 t;
二.已知程序和数据
create table pm_ci
(ci_id varchar(20) primary key,
stu_ids varchar(100)); insert into pm_ci values('','1,2,3,4');
insert into pm_ci values('','1,4'); create table pm_stu
(stu_id varchar(20) primary key,
stu_name varchar(20));
insert into pm_stu values('','张三');
insert into pm_stu values('','李四');
insert into pm_stu values('','王五');
insert into pm_stu values('','赵六');
要求根据下图得到相应的sql查询：
答案：
select ci_id,wm_concat(stu_name) names
from (select ci_id,stu_name
from pm_ci c,pm_stu s
where instr(c.stu_ids,s.stu_id)>0)
group by ci_id;
分析：
（1）wm_contat：列合并函数，能将查询到的结果合并到一起，并默认使用，隔开。
（2）instr函数：字符串查找函数，instr(c.stu_ids,s.stu)的意思就是在c.stu_ids中寻找字符串s.stu，如果找到了就返回大于零的一个数，代表该字串所在的位置，如果没有找到，则返回0。
练习题1：找到员工表中工资最高的前三名，如下格式：
分析：这道题使用子查询就能解决，实际上是Oracle分页的知识点。一个重要的伪列：rownum，该伪列代表了行号。
使用rownum行号的重要注意事项：
（1）行号永远按照默认的顺序生成，什么叫做默认的顺序？也就是说如果使用order by进行了排序，不会改变原来的行号。
（2）rownum只能使用rownum<或者rownu<=的形式，不能使用rownum>或者rownum>=的形式，原因是rownum只能从1开始依次递增。
答案：
select rownum,empno,ename,sal from (select empno,ename,sal from emp order by sal desc) where rownum<=3
这个问题是的核心思想和Oracle分页的核心思想并没有什么不同，那么Oracle中没有limit语法，该怎么分页呢？关键还是在于rownum的使用上。
虽然不能直接对rownum进行>=的操作，但是可以在主查询中对子查询的rownum进行该操作。
查找员工表中工资为5-8名的员工信息：
select * from (select rownum r,empno,ename,sal from (select empno,ename,sal from emp order by sal desc) where rownum<=8) where r>=5
注意子查询中rownum起别名是为了防止和主查询中使用的rownum起冲突。
练习题2：找到员工表中薪水大于本部门平均薪水的员工。
效果图如下：
答案：使用子查询解决该问题
select empno,ename,sal,(select avg(sal) from emp where deptno=e.deptno) avgsal from emp e where sal> (select avg(sal) from emp where deptno=e.deptno)
练习题3：统计每年入职的员工个数。
效果图如下：
使用
select distinct to_char(hiredate,'yyyy') from emp ;
可以得到实际上只有四个入职年份，所以只需要针对这四个入职年份进行选择就可以了，这里使用decode函数进行选择。
答案：
select count(*) Total ,sum(decode(to_char(hiredate,'yyyy'),'',1,0)) "1980",sum(decode(to_char(hiredate,'yyyy'),'',1,0)) "1981",sum(decode(to_char(hiredate,'yyyy'),'',1,0)) "1982",sum(decode(to_char(hiredate,'yyyy'),'',1,0)) "1987" from emp
该道题关键就在于函数的使用：decode、sum、to_char。