排序查询

语法：
select from order by column_name1,column_name2… [asc]/desc
order by :排序的关键字
asc:升序 默认为升序
desc:降序 (在sqlplus中 语法为:desc table_name.展示当前表结构)

查询员工信息,根据工资排序
select * from emp order by sal

查询20号部门工资大于1300的员工信息,结果按照员工编号降序排列
select * from emp where deptno=20 and sal>1300 order by empno desc

查询没有佣金的员工信息,结果按部门编号升序排列,如果部门编号相同,则按照工资降序排列
select * from emp where comm is null order by deptno,sal desc

补充:
可用别名做排序:
select ename,sal s,deptno,comm from emp order by s
select ename,sal s,deptno d,comm from emp order by s,d

可用数字做排序:
select ename,sal,deptno,comm from emp order by 3
select ename,sal,deptno,comm from emp order by 2
select ename,sal,deptno,comm from emp order by 5-----数目不匹配
select * from emp order by 8,1

null 可用nulls first/last 控制顺序
select * from emp order by comm

select * from emp order by comm desc

查询员工佣金,升序排列,将null放在最前面
select * from emp order by comm nulls first-----空值放在最前 nulls first

查询员工佣金,降序排列,将null放在最后面
select * from emp order by comm desc nulls last -----空值放在最后 nulls last

rowid:行编码
select * from emp where rowid=‘AAAR3sAAEAAAACXAAB’
AAASqLAAEAAAAK7AAB

create table emp1 as select * from emp

select * from emp1

select e.*,rowid from emp e

select ename,sal,rowid from emp

rownum:伪列
对查询结果进行从1开始,依次增加的自然数
select ename,rownum from emp;
select ename,rownum from emp where sal>2000

select ename,rownum from emp where sal>2000 and rownum=2

select ename,rownum from emp where sal>2000 and rownum=1
select ename,rownum from emp where sal>2000 and rownum>=1
select ename,rownum from emp where sal>2000 and rownum>1
select ename,rownum from emp where sal>2000 and rownum>0
select ename,rownum from emp where sal>2000 and rownum>=0
select ename,rownum from emp where sal>2000 and rownum>=2
select ename,rownum from emp where sal>2000 and rownum<=1
select ename,rownum from emp where sal>2000 and rownum<=5
select ename,rownum from emp where sal>2000 and rownum<1
select ename,rownum from emp where sal>2000 and rownum<5

rownum 作为条件不能大于等于比1大的数
分页查询:
查询工资最高的员工姓名,工资
select ename,sal from emp where rownum=1 order by sal desc

查询工资前三名的员工姓名,工资
select ename,sal from emp where rownum<=3 order by sal desc----错误,先rownum<=3,后排序

解决:
select * from emp order by sal desc-----先将工资降序排列
select e.* ,rownum from (select * from emp order by sal desc) e where rownum<=3 ----将查询结果作为临时表

求工资排名3到5位的员工信息
方法一:
select * from emp order by sal desc----先将工资降序排列
select e.,rownum from (select * from emp order by sal desc) e where rownum<=5 —求出前五名
select e.,rownum from (select * from emp order by sal desc) e where rownum<=5 order by sal —将前五名工资升序排列
select * from (select e.*,rownum from (select * from emp order by sal desc) e where rownum<=5 order by sal) where rownum<=3 —求出前三名

方法二:
select * from emp order by sal desc----先将工资降序排列
select e.,rownum from (select * from emp order by sal desc) e
select * from (select e.,rownum r from (select * from emp order by sal desc) e ) where r>=3 and r<=5----对伪列进行命名,当作真实列

求emp表中第10行及之后的数据?
求工资升序排名5-10位的员工信息?

select ename from emp order by sal
select * from emp where ename in (select ename from emp order by sal)—where 字句不能加order by