17,082
社区成员
发帖
与我相关
我的任务
分享
with tab1 as (
select t1.balance_date, sum(t1.balance) balance
from ebank_balance_indirects t1
group by t1.balance_date
)
,tab2 as (
select t1.*, row_number() over(order by t1.balance_date) rn from tab1 t1
where t1.balance between 2 and 20
and t1.balance_date between to_date('2018-11-01', 'yyyy-mm-dd') and to_date('2018-11-18', 'yyyy-mm-dd')
)
select *
from tab2 t1
start with t1.rn = 1
connect by prior t1.rn + 1 = t1.rn
and prior t1.balance_date + 2 >= t1.balance_date
;
create table EBANK_BALANCE_INDIRECT
(
PK_EBANK_BALANCE NUMBER(19) not null,
BALANCE_DATE DATE,
BALANCE NUMBER(19,4)
);
数据sql:
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('1', to_date('01-11-2018', 'dd-mm-yyyy'), '22');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('1', to_date('01-11-2018', 'dd-mm-yyyy'), '22');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('2', to_date('02-11-2018', 'dd-mm-yyyy'), '21');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('3', to_date('03-11-2018', 'dd-mm-yyyy'), '3');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('4', to_date('04-11-2018', 'dd-mm-yyyy'), '2');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('5', to_date('05-11-2018', 'dd-mm-yyyy'), '4');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('6', to_date('06-11-2018', 'dd-mm-yyyy'), '0');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('7', to_date('07-11-2018', 'dd-mm-yyyy'), '5');
insert into ebank_balance_indirects (PK_EBANK_BALANCE, BALANCE_DATE, BALANCE)
values ('8', to_date('08-11-2018', 'dd-mm-yyyy'), '6');
数据信息:
钱数设为2~20,连续天数设为2,日期设为2018-11-01—2018-11-8 连续天数以起始时间为准,超过两天或等于两天 并且满足日期和金额显示数据。
期望得到这样的结果
select date'2018-10-02' b_d, 23 ba from dual union all
select date'2018-11-02' b_d, 23 ba from dual union all
select date'2018-11-03' b_d, 233 ba from dual union all
select date'2018-11-04' b_d, 23 ba from dual union all
select date'2018-11-04' b_d, 23 ba from dual union all
select date'2018-11-07' b_d, 23 ba from dual union all
select date'2018-11-08' b_d, 23 ba from dual union all
select date'2018-11-09' b_d, 23 ba from dual
钱数设为1~100,连续天数设为2,并且期望得到这样的结果
select date'2018-11-02' b_d, 23 ba from dual union all
select date'2018-11-04' b_d, 23 ba from dual union all
select date'2018-11-04' b_d, 23 ba from dual
简化的程度代表你的诚意!
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000071', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('01-12-2018', 'dd-mm-yyyy'), '', '', 'ICBCPB', to_date('01-11-2018 11:20:03', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:20:03.078', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000101', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('30-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:21:34', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:21:34.869', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000070', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('30-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:20:03', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:20:03.078', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000100', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('29-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:21:34', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:21:34.869', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000069', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('29-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:20:03', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:20:03.078', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000099', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('28-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:21:34', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:21:34.869', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000068', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('28-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:20:03', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:20:03.078', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000067', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('27-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:20:03', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:20:03.078', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000098', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('27-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:21:34', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:21:34.869', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000128', '衡水市分公司', '2', '中国工商银行', '100148614458', '人民币', '', '1', '', '', to_date('27-11-2018', 'dd-mm-yyyy'), '', '', 'CB', to_date('12-11-2018 15:03:29', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-12 15:03:29.439', '10.251.8.150', '1007201100000009467', '', '1', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000162', '中国网络通信有限公司衡水市分公司', '中国银行衡水分行营业部');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE, CLEARING_CORP, BRANCH_PROPERTY, BANK_TYPE_CODE, DOWNLOAD_TIME, DR, TS, MODIFY_IP, MODIFY_USER_ID, DEF1, DEF2, DEF3, DEF4, DEF5, DEF6, DEF7, DEF8, DEF9, DEF10, DEF11, DEF12, DEF13, DEF14, DEF15, DEF16, DEF17, DEF18, DEF19, DEF20, PK_CORP, ACCOUNTNAME, ACCOUNTBANK)
values ('7001901400000000097', '中国山东分公司', '0', '中国银行', '1234567890', '人民币', '', '23', '', '', to_date('26-11-2018 08:00:00', 'dd-mm-yyyy hh24:mi:ss'), '', '', 'ICBCPB', to_date('01-11-2018 11:21:34', 'dd-mm-yyyy hh24:mi:ss'), '', '2018-11-01 11:21:34.869', '10.251.14.87', '1007201100000009467', '', '23', '1', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1', '', '', '1000201100000000403', 'test1上级归集', 'test1上级归集');
insert into ebank_balance_indirect (PK_EBANK_BALANCE, CORP_NAME, COLLECTION_PAY_FLAG, BANK_TYPE, BANK_ACCOUNT, CURRENCY_TYPE, YESTERDAY_BALANCE, BALANCE, USABLE_BALANCE, BANK_NAME, BALANCE_DATE,
-- Create table
create table EBANK_BALANCE_INDIRECT
(
PK_EBANK_BALANCE NUMBER(19) not null,
CORP_NAME VARCHAR2(100),
COLLECTION_PAY_FLAG CHAR(1),
BANK_TYPE VARCHAR2(30),
BANK_ACCOUNT VARCHAR2(30),
CURRENCY_TYPE VARCHAR2(50),
YESTERDAY_BALANCE NUMBER(19,4),
BALANCE NUMBER(19,4),
USABLE_BALANCE NUMBER(19,4),
BANK_NAME VARCHAR2(100),
BALANCE_DATE DATE,
CLEARING_CORP VARCHAR2(100),
BRANCH_PROPERTY VARCHAR2(256),
BANK_TYPE_CODE VARCHAR2(50),
DOWNLOAD_TIME DATE,
DR CHAR(1),
TS VARCHAR2(24),
MODIFY_IP VARCHAR2(45),
MODIFY_USER_ID NUMBER(19),
DEF1 VARCHAR2(100),
DEF2 VARCHAR2(100),
DEF3 VARCHAR2(100),
DEF4 VARCHAR2(100),
DEF5 VARCHAR2(100),
DEF6 VARCHAR2(100),
DEF7 VARCHAR2(100),
DEF8 VARCHAR2(100),
DEF9 VARCHAR2(100),
DEF10 VARCHAR2(100),
DEF11 VARCHAR2(100),
DEF12 VARCHAR2(100),
DEF13 VARCHAR2(100),
DEF14 VARCHAR2(100),
DEF15 VARCHAR2(100),
DEF16 VARCHAR2(100),
DEF17 VARCHAR2(100),
DEF18 VARCHAR2(100),
DEF19 VARCHAR2(100),
DEF20 VARCHAR2(100),
PK_CORP NUMBER(19),
ACCOUNTNAME VARCHAR2(100),
ACCOUNTBANK VARCHAR2(100)
)
tablespace DATA_TM
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 4
next 4
minextents 1
maxextents unlimited
pctincrease 0
);