常用的SQL函数

  1. row_number() over()
  • 分组排序功能
  • over()里头分组以及排序的执行晚于 where 、group by、 order by 的执行
  • over() 函数的写法
    1. -- 先对cno 中相同的进行分区，在cno 中相同的情况下对degree 进行排序
    2. over（partition by cno order by degree ）
  1. partition by ()
  • 分区函数，属于聚合函数
  1. group by()
  • 分组函数
  1. order by()
  2. rank()函数与dense_rank() 的用法区别 ```plsql SELECT * FROM (select sno,cno,degree,
    1. rank()over(partition by cno order by degree desc) mm 
    2. from score)
    where cno = ‘3-245’
  2. - dense_rank()
  3. ```plsql
  4. SELECT * 
  5. FROM   (select sno,cno,degree,
  6.        dense_rank()over(partition by cno order by degree desc) mm 
  7.        from score) 
  8. where cno = '3-245'
  • rank()和dense_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense_rank()是非跳跃排序，有两个第一名时接下来是第二名
  1. 分区函数partition by () 与 rank() 的用法区别
  • rank() ```plsql SELECT * FROM (select sno,cno,degree,
    1.     rank()over(partition by cno order by degree desc) mm 
    2.     from score)
    where mm = 1;
  2. - row_number()
  3. ```plsql
  4. SELECT * 
  5. FROM   (select sno,cno,degree,
  6.        row_number()over(partition by cno order by degree desc) mm 
  7.        from score) 
  8. where mm = 1;
  • 当排序结果存在并列的时候，row_number() 只返回一个结果，此时需要用rank()函数。
  1. datediff()
  • 获取两个日期的时间间隔
  • datediff(date_format(b.answer_time,’yyyy-MM-dd’),date_format(a.emp_assign_time,’yyyy-MM-dd’))
  1. date_format()
  • 日期格式化
  • date_format(b.answer_time,’yyyy-MM-dd’)

窗口函数

  1. -- 基本用法：窗口函数 over(partition by 分组字段 order by 排序字段)
  2. select *,rank() over(partition by id order by score)as ranking from student;
  4. select *,dense_rank() over(partition by id order by score)as ranking from student;
  6. select *,row_number() over(partition by id order by score)as ranking from student;
  8. -- 分布函数：percent_rank()、cume_dist()
  9. select *,percent_rank() over(partition by id order by score)as ranking from student;
  11. select *,cume_dist() over(partition by id order by score)as ranking from student;
  13. -- 前后函数：lag()、lead()
  14. select *,lag(score,1) over(partition by id order by score)as score from student;
  16. select *,lead(score,1) over(partition by id order by score)as score from student;
  18. -- 头尾函数：first_value()、last_value()
  19. select *,first_value(score) over(partition by id order by score)as score from student;
  20. select *,last_value(score) over(partition by id order by score)as score from student;