over(partition by) 记录

SELECT t.*, sum(t.sal) over(partition by t.name,t.part)
        as 本部门工资总和
  FROM ATEST t

// over()表示没有条件的所有的sal相加的结果

SELECT t.*, t.sal/sum(t.sal) over(),sum(t.sal) over() as sum
  FROM ATEST t
  //用下面的方法可以获取按照part来排列每个part的sal总和，最后只得到part-sum(sal)为一对一的关系
  select * from
(
  select row_number()  over(partition by t.part order by t.emp) as nx,
  t.*  ,sum(t.sal) over(partition by t.name,t.part)
        as 本部门工资总和 FROM ATEST t ) where nx=1

转载于:https://www.cnblogs.com/yaojian/archive/2010/10/15/1852369.html