下面有这样一个表的数据,在这个表格中,有2种数据:
a) S=0 的数据是一条独立的记录,
b) S=1 的数据不是独立的记录,它后面可能跟着其它 S=1 的,但肯定会有一个S=0 的结尾,这几条记录是一组。不管是一条独立的记录还是一组非独立的记录,它们的 P+I 相加是相等的。比如:
SEQ =1:P+I = A = 100+20 = 120.
SEQ =2:P+I = A = 101+19 = 120.
SEQ =3+4+5: (0+18) + (0+18)+ (66+18) = 120.
SEQ =6: = 103 + 17 = 120.
SEQ =7+8: = (0+16) + (88+16) = 120.我的问题是,有没有办法在 Oracle 里面用一条 SQL 把独立的记录和一组记录求和成如下形式的结果:
其中的 SEQ 列碰到一组的记录取该组的最大值,S 的值取最小值。
1, 100, 20, 120, 0
2, 101, 19, 120, 0
5, 66, 54, 120, 0
6, 103, 17, 120, 0
9, 88, 32, 120, 0create table T1 (
SEQ number(10),
P number(25,5),
I number(25,5),
A number(25,5),
S number(1)
);insert into T1 values (1, 100, 20, 120, 0);
insert into T1 values (2, 101, 19, 120, 0);
insert into T1 values (3, 0 , 18, 18, 1);
insert into T1 values (4, 0 , 18, 18, 1);
insert into T1 values (5, 66 , 18, 84, 0);
insert into T1 values (6, 103, 17, 120, 0);
insert into T1 values (7, 0 , 16, 16, 1);
insert into T1 values (8, 88 , 16, 104, 0);select * from T1;
a) S=0 的数据是一条独立的记录,
b) S=1 的数据不是独立的记录,它后面可能跟着其它 S=1 的,但肯定会有一个S=0 的结尾,这几条记录是一组。不管是一条独立的记录还是一组非独立的记录,它们的 P+I 相加是相等的。比如:
SEQ =1:P+I = A = 100+20 = 120.
SEQ =2:P+I = A = 101+19 = 120.
SEQ =3+4+5: (0+18) + (0+18)+ (66+18) = 120.
SEQ =6: = 103 + 17 = 120.
SEQ =7+8: = (0+16) + (88+16) = 120.我的问题是,有没有办法在 Oracle 里面用一条 SQL 把独立的记录和一组记录求和成如下形式的结果:
其中的 SEQ 列碰到一组的记录取该组的最大值,S 的值取最小值。
1, 100, 20, 120, 0
2, 101, 19, 120, 0
5, 66, 54, 120, 0
6, 103, 17, 120, 0
9, 88, 32, 120, 0create table T1 (
SEQ number(10),
P number(25,5),
I number(25,5),
A number(25,5),
S number(1)
);insert into T1 values (1, 100, 20, 120, 0);
insert into T1 values (2, 101, 19, 120, 0);
insert into T1 values (3, 0 , 18, 18, 1);
insert into T1 values (4, 0 , 18, 18, 1);
insert into T1 values (5, 66 , 18, 84, 0);
insert into T1 values (6, 103, 17, 120, 0);
insert into T1 values (7, 0 , 16, 16, 1);
insert into T1 values (8, 88 , 16, 104, 0);select * from T1;
from t1 t
start with not exists (select 1
from t1
where seq = t.seq - 1
and s = 1)
connect by seq = prior seq + 1
and prior s = 1
group by rownum - level
order by seq
#3 楼的,这里面的 SEQ 是业务字段不是系统自动生成的 sequence,存储次序可能保证不也,但它的值是按前面说的规律处理的。
你的SEQ 的随机的?
贴点数据和你想要的结果......
SEQ P I TOTAL S
1 100 20 120 0
2 101 19 120 0
5 66 54 120 0
6 103 17 120 0
8 88 32 120 0
WITH tt AS
(SELECT a.*, decode(ps + s, 2, 1, ps + s) ss
FROM (SELECT t1.*, lag(s, 1, 0) over(ORDER BY seq) ps FROM t1) a)
SELECT seq, p, i, a, s
FROM (SELECT seq,
row_number() over(PARTITION BY seq - rn ORDER BY seq DESC) r,
SUM(p) over(PARTITION BY seq - rn) p,
SUM(i) over(PARTITION BY seq - rn) i,
SUM(a) over(PARTITION BY seq - rn) a,
s
FROM (SELECT tt.*, rownum rn FROM tt WHERE ss = 1 ORDER BY seq) b)
WHERE r = 1
UNION
SELECT seq, p, i, a, s FROM tt WHERE ss = 0