所用数据:
SELECT a.deptno, a.employename, a.salary
FROM t_salary a
000001 李可 1000
000001 李强 2000
000001 杨彦军 4000
000002 童家道 3000
000002 姜文 3000
000002 罗文 3000
000003 窨嫡 3000
000003 童家道 3000
000003 童家道 3000
000004 于名 4000
使用代码:
SELECT A.deptno, A.employename,A.salary,
--1 按照名称进行分区,同时按照名称进行合计
SUM(A.salary)OVER(PARTITION BY A.employename) AS SUM_INC_ONLY,
--2 按照名称进行累计
SUM(A.salary)OVER(ORDER BY A.employename) AS SUM_INC,
--3 和 1 效果相同
SUM(A.salary)OVER(PARTITION BY A.employename ORDER BY A.employename) AS SUM_INC_NAME,
--4 按照部门分组,部门内进行合计。名称相同时进行累计
SUM(A.salary)OVER(PARTITION BY A.deptno ORDER BY A.employename) AS SUM_INC_DEP,
--5 按照部门,名称分组,部门名称相同时进行合计
SUM(A.salary)OVER(PARTITION BY A.deptno,A.employename ) AS SUM_INC_DEP_NAM
FROM t_salary A
所得结果:
DEPTNO EMPLOYENAME SALARY SUM_INC_ONLY SUM_INC SUM_INC_NAME SUM_INC_DEP SUM_INC_DEP_NAM
000002 姜文 3000 3000 3000 3000 3000 3000
000001 李可 1000 1000 4000 1000 1000 1000
000001 李强 2000 2000 6000 2000 3000 2000
000002 罗文 3000 3000 9000 3000 6000 3000
000002 童家道 3000 9000 18000 9000 9000 3000
000003 童家道 3000 9000 18000 9000 6000 6000
000003 童家道 3000 9000 18000 9000 6000 6000
000001 杨彦军 4000 4000 22000 4000 7000 4000
000004 于名 4000 4000 26000 4000 4000 4000
000003 窨嫡 3000 3000 29000 3000 9000 3000
分享到:
相关推荐
Oracle中的SUM条件查询 1、按照区域编码分组查询区域编码、IPTV_NBR不为空的数量、ACC_NBR不为空的数量、所有用户数量 SELECT AREA_CODE, SUM ( CASE WHEN IPTV_NBR IS NULL or IPTV_NBR = '' THEN 0 ELSE 1 END ),...
h is a weighted sum over H: 加权和 h = sigma(j = 0 to n-1) alpha(j) * H(j) weight alpha[i, j] for each hj is computed as follows: H = [h1,h2,...,hn] M = tanh(H) alhpa = softmax(w.transpose * M) h# =...
Part scientific exploration, part philosophy, this unique book touches upon such diverse topics as dark matter, Feynman's "sum over paths", the quantum observer, and the soul. It is aimed at anyone ...
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和 sum(sal) over (partition by deptno) 按部门求总和 sum(sal) over (order by deptno,ename) 不按部门“连续”求总和 sum(sal) over () ...
第六篇 著名函数之分析函数 1、AVG([DISTINCT|ALL] expr) OVER(analytic_clause) 计算平均值。 例如: ...SELECT col, sum(value) OVER(PARTITION BY col ORDER BY col) FROM tmp1 ORDER BY col;
use function over series平均,最后 x 分钟/小时/天的总和 Use TimeSeriesBucket of bucksize of x Mins/Hours/Days with elementSize of 5 sec(data definition)alternatively, can use Function avg, sum over a...
ei ej = sum over k m[i,j,k] ek 代数单位向量由 ei u = u ei = ei for each i ArtinWedderburn取一个张量和单位矢量,并计算代数的不可约表示(这些仅在共轭之前定义)。 它会在工作时跟踪数值误差,因此您可以查看...
代码如下:Sum() Over ([Partition by ] [Order by ]) Sum() Over ([Partition by ] [Order by ] Rows Between Preceding And Following) Sum() Over ([Partition by ] [Order by ] Rows Between ...
问题点:在sum对window函数执行时,如果有重复数据,会直接把相同的数据相加,并不是逐步相加。 问题描述 数据:在一个成绩表中,有三个个字段:学生s_id,课程c_id,成绩s_score。 查询条件查询每个课程的学生成绩...
排列(rank())函数。这些排列函数提供了定义一个集合(使用 PARTITION ...sum(sal)over(order by ename) sum1, sum(sal)over() sum2, 100* round(sal/sum(sal)over(),4) bal% from emp 结果如下: DEPTNO ENAME S
A tighter bound for the character sum of primitive sequences over residue rings modulo square-free odd integers
聚合函数可以是:sum,count,avg,max,min,first_value,last_value,rank,dense_rank ,row_number, ratio_to_report Over不能单独使用,用来制定数据窗口大小 Partition by表示分类数据集合,在此集合上的运算 Order by...
前言: ...例如:SUM() Over() 累加值、AVG() Over() 平均数 MAX() Over() 最大值、MIN() Over() 最小值 具体介绍: 下面模拟工作中通过开窗函数代替游标的例子,通过期初余额与单据的预收金额、应收金额
over the web, As of writing this readme there is Haskell 99: 28 finished + Huffman Coding LeetCode: LC1: 2Sum [Easy] LC2: Add Two Numbers [Medium] LC3: Longest Substring Without Repeating Characters ...
utl按ID将滚动和和计数超过3天的窗口 滚动移动总和,并按ID计数超过3天的时间滚动移动总和,并按ID计数超过3天的时间 ...https://github.com/rogerjdeangelis/utl-rolling-moving-sum-and-count-over
This partition minimizes the sum, over all % clusters, of the within-cluster sums of point-to-cluster-centroid % distances. Rows of X correspond to points, columns correspond to % variables. KMEANS ...
Sum frequency generation using a fiber ring resonator was demonstrated. SFG power of over 40 |iW was generated. Efficient SFG was produced for round-trip losses up to -20.5 dB, which is useful for ...
%number of iterations over which the results are going to be averaged N_iter = 15; for iter = 1:N_iter data = round(rand(N_bits,1));%random data bits %channel coding using rate 1/2 convolutional ...
Normal traffic signals are transported bidirectionally over spans: an incoming tributary travels in one direction of the working channels while its associated outgoing tributary travels in the ...