数据库开窗函数的应用

最新推荐文章于 2023-06-08 15:31:55 发布

yurgqg

最新推荐文章于 2023-06-08 15:31:55 发布

阅读量223

点赞数

文章标签： sql 数据库

本文链接：https://blog.csdn.net/qq_44871403/article/details/126280671

版权

相关sql语句

SELECT A
	.*,
--对分区排序内的数据进行行号标记，不区分重复行(dense_rank()区分重复行，rank()也不区分重复行)
	ROW_NUMBER ( ) OVER ( PARTITION BY A.department_name ORDER BY A.sal DESC ) AS ord,
--分区后求最大的
	MAX ( A.sal ) OVER ( PARTITION BY A.department_name ) AS max_sal,
--分区后求最小的
	MIN ( A.sal ) OVER ( PARTITION BY A.department_name ) AS min_sal,
--求和，有order by 时是连续求和
	SUM ( A.sal ) OVER ( PARTITION BY A.department_name ) AS sum_sal,
--lag(落后,滞后)于某字段，1代表偏移量，即分组的排序的列表中上一行的字段的id
	LAG ( A.ID, 1 ) OVER ( PARTITION BY A.department_name ORDER BY A.sal DESC ) AS front_id,
--lead领导的意思，领导的一个人，即分组的排序的列表的下一行的字段的id
	LEAD ( A.ID, 1 ) OVER ( PARTITION BY A.department_name ORDER BY A.sal DESC ) AS next_id 
FROM
	lx_05_salary A;