3,491
社区成员
发帖
与我相关
我的任务
分享
create table TMP_T
(
id1 VARCHAR2(60) not null,
id2 VARCHAR2(60),
money1 NUMBER(16,2) not null,
money2 NUMBER,
id3 VARCHAR2(60),
money3 NUMBER(16,2)
)
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008407', 'BI_BL_0000000000060041', 900000.00, 900000, 'BI_BL_0000000000060041', 900000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008408', 'BI_BL_0000000000060023', 210000.00, 210000, 'BI_BL_0000000000060023', 210000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060046', 520000.00, 50000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060044', 520000.00, 80000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060036', 520000.00, 40000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060040', 520000.00, 10000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060047', 520000.00, 40000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060042', 520000.00, 50000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060038', 520000.00, 40000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060039', 520000.00, 30000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 1833.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 4000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 4184.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 34400.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 37000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 324.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 1400.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060045', 520000.00, 100000, 'BI_BL_0000000000060045', 3600.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060043', 520000.00, 40000, 'BI_BL_0000000000060043', 2160.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060043', 520000.00, 40000, 'BI_BL_0000000000060043', 1490.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060043', 520000.00, 40000, 'BI_BL_0000000000060043', 916.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060043', 520000.00, 40000, 'BI_BL_0000000000060043', 27200.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060043', 520000.00, 40000, 'BI_BL_0000000000060043', 1000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060043', 520000.00, 40000, 'BI_BL_0000000000060043', 7000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008409', 'BI_BL_0000000000060037', 520000.00, 40000, 'BI_BL_0000000000060037', 40000.00);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008410', 'BI_BL_0000000000060053', 421354.80, 421354.8, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008411', 'BI_BL_0000000000060052', 424000.00, 424000, null, null);
insert into tmp_t (ID1, ID2, MONEY1, MONEY2, ID3, MONEY3)
values ('ABI_ADD_008412', 'BI_BL_0000000000061314', 180000.00, 180000, null, null);
select decode(id1,lag(id1) over(order by id1),null,id1),
decode(id1,lag(id1) over(order by id1),decode(money1,lag(money1) over(order by id1),null,money1),money1),
decode(id2,lag(id2) over(order by id2),null,id2),
decode(id2,lag(id2) over(order by id2),decode(money2,lag(money2) over(order by id2),null,money2),money2),
decode(id3,lag(id3) over(order by id3),null,id3),
decode(id3,lag(id3) over(order by id3),decode(money3,lag(money3) over(order by id3),null,money3),money3)
from tmp_t
order by id1
select decode(id1,lag(id1) over(order by id1),null,id1),
decode(money1,lag(money1) over(order by id1),null,money1),
decode(id2,lag(id2) over(order by id2),null,id2),
decode(money2,lag(money2) over(order by id1),null,money2),
decode(id3,lag(id3) over(order by id3),null,id3),
decode(money3,lag(money3) over(order by id3),null,money3)
from tmp_t
order by id1
select
case when id1<>nvl(lag(id1)over(order by id1),0) then id1 else null end id1,
case when money1<>nvl(lag(money1)over(order by id1),0) then money1 else null end money1,
case when id2<>nvl(lag(id2)over(order by id1),0) then id2 else null end id2,
case when money2<>nvl(lag(money2)over(order by id1),0) then money2 else null end money2,
case when id3<>nvl(lag(id3)over(order by id1),0) then id3 else null end id3,
case when money3<>nvl(lag(money3)over(order by id1),0) then money3 else null end money3
from (
select distinct id1,money1,id2,money2,id3,money3
from TMP_T
order by id1,id2,id3
)
select
case when id1<>nvl(lag(id1)over(order by id1),0) then id1 else null end id1,money1,
case when id2<>nvl(lag(id2)over(order by id1),0) then id2 else null end id2,money2,
case when id3<>nvl(lag(id3)over(order by id1),0) then id3 else null end id3,money3
from (
select distinct id1,money1,id2,money2,id3,money3
from TMP_T
order by id1,id2,id3
)