Oracle ruwnum语句,rank等排序,及Group By分组

在ORACLE中经常是用ORDER BY跟ROWNUM的组合来实现SELECT TOP N的查询。伪字段，可以用于限制查询返回的总行数。

1.rownum查询

|
|--(1) rownum 对于等于某值的查询条件
|		SQL> select rownum,id,name from student where rownum=1;		//找到学生表中第一条学生的信息
|		SQL> select rownum,id,name from student where rownum =2; 	//条件为false
|		
|--(2)rownum对于大于某值的查询条件
|		SQL>select * from(select rownum no ,id,name from student) where no>2;
|		        NO ID     NAME
|		---------- ------ ---------------------------------------------------
|		         3 200003 李三
|		         4 200004 赵四
|		
|--(3)rownum对于小于某值的查询条件
|		SQL> select rownum,id,name from student where rownum <3;
|		    ROWNUM ID     NAME
|		---------- ------ ---------------------------------------------------
|		        1 200001 张一
|		        2 200002 王二
|		
|--(4)查询rownum在某区间的数据(必须使用子查询)
|     	SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;
|     	        NO ID     NAME
|     	---------- ------ ---------------------------------------------------
|     	         2 200002 王二
|     	         3 200003 李三
| 

2.rownum查询+排序

|  
|--错误姿势(rownum的是在取数据的时候产生的序号，排序时rownum不变)
|		SQL> select rownum ,id,name from student order by name;
|		    ROWNUM ID     NAME
|		---------- ------ ---------------------------------------------------
|		         3 200003 李三
|		         2 200002 王二
|		         1 200001 张一
|		         4 200004 赵四
|				 
|--正确姿势(把排序作为子查询,rownum查询作为主查询)
|		SQL> select rownum ,id,name from (select * from student order by name) where rownum<4;
|		    ROWNUM ID     NAME
|		---------- ------ ---------------------------------------------------
|		         1 200003 李三
|		         2 200002 王二
|		         3 200001 张一

3.Oracle排序分析函数

|
|--rank 	 : 分数相同排名相同，排名有跳跃。
|--dense_rank: 分数相同排名相同，排名不跳跃。
|--row_number: 分数相同排名不同，按记录的顺序递增。
|--ntile     : 分为n组，后面的组可能少分。

select
	score
   ,rank() 		 over(order by score desc) rank               
   ,dense_rank() over(order by score desc) dense_rank   
   ,row_number() over(order by score desc) row_number   
   ,ntile(3) 	 over(order by score desc) group_tile      
from s_score;
 _________________________________________________
|Score	Rank	Dense_Rank	Row_Number	Group_tile|
|99		 1		 1			1			1         |
|98      2       2			2			1         |
|98      2       2			3			2         |
|98      2       2			4			2         |
|80      5       3			5			3         |
|66.5    6       4			6			3         |
|_________________________________________________|

4.Oracle分组函数及group by

|
|--AVG(忽略空值),SUM(忽略空值) : avg(expr)  等价于  sum(expr)/count(expr))
|--MIN 和 MAX函数 			   ：可以对任意数据类型的数据使用 MIN 和 MAX 函数。 //SELECT MIN(hire_date), MAX(hire_date) FROM employees;
|--COUNT(expr)(忽略空值)       ：返回expr不为空的记录总数。//如需算上空值：count(nvl(expr,0))
|
|--Group by ：|--① WHERE一定放在FROM后面
|             |--② 在SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。
|             |--③ 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
|             |--④ 使用多个列分组
|             |--⑤ 可以在 HAVING 子句中使用组函数。//注意：不能在 WHERE 子句中使用组函数。
|             |--⑥ 可以在order by 子句中使用组函数