|
drop table t_emp;
create table t_emp(id number(10),name varchar2(10),salary number(10,2));
insert into t_emp values(1,'jack',10000.50);
insert into t_emp values(2,'jack2',8000.50);
insert into t_emp values(3,'jack3',120000);
insert into t_emp values(4,'jack4',5000);
commit;
select * from t_emp;
--查询工资第三高的
select rn,name,salary from (select rownum rn,name,salary from
(select name,salary from t_emp order by salary desc)) where rn=3;
--查询倒数第2的
select rn,name,salary from (select rownum rn,name,salary from
(select name,salary from t_emp order by salary)) where rn=2;
---------------------------------------------------------------------------------模拟distinct-------------------------------------------------
这个题目和剔除重复行是一样的概念,剔除重复行是找出有那些重复的,一般保留一条,模拟distinct,我们就是找出重复行中的一条就可以了,
其他不重复的同样加上
-------------------------------------test---------------------------------------------------------------------------------------------------------------
drop table t_distinct;
create table t_distinct(id number,name varchar2(10));
insert into t_distinct values(1,'dj');
insert into t_distinct values(1,'dj');
insert into t_distinct values(1,'dj');
insert into t_distinct values(2,'dj');
insert into t_distinct values(3,'dj1');
insert into t_distinct values(3,'dj');
insert into t_distinct values(4,'dj1');
insert into t_distinct values(5,'dj2');
commit;
--模拟distinct
select t1.id,t1.name from t_distinct t1 where t1.rowid =
(select max(t2.rowid) from t_distinct t2 where t1.id=t2.id and t1.name=t2.name);
---下面的也行
select unique id,name from t_distinct;
select id,name from t_distinct group by id,name;
[ 本帖最后由 dingjun123 于 2008-8-1 10:29 编辑 ] |
|