SQL滚动统计

SELECT ID, PAY_TIME, TYPE, FEES, PAY, 
SUM(NVL(FEES, 0)) OVER(ORDER BY PAY_TIME DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) TOTAL_FEES,
SUM(NVL(PAY, 0)) OVER(ORDER BY PAY_TIME DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) TOTAL_PAY,
(SUM(NVL(FEES, 0)) OVER(ORDER BY PAY_TIME DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - SUM(NVL(PAY, 0)) OVER(ORDER BY PAY_TIME DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)) balance,
CONTENT, ACTIVITY, PEOPLE 
FROM OMS_ASSOCIATION_FEES WHERE IS_DELETED = 0 ORDER BY PAY_TIME DESC,ID DESC;


SELECT ID, PAY_TIME, TYPE, FEES, PAY, 
SUM(FEES) OVER(ORDER BY PAY_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTAL_FEES,
SUM(PAY) OVER(ORDER BY PAY_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) TOTAL_PAY,
(SUM(NVL(FEES, 0)) OVER(ORDER BY PAY_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - SUM(NVL(PAY, 0)) OVER(ORDER BY PAY_TIME ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) balance,
CONTENT, ACTIVITY, PEOPLE 
FROM OMS_ASSOCIATION_FEES WHERE IS_DELETED = 0 ORDER BY PAY_TIME,ID;


create table OMS_ASSOCIATION_FEES
(
    ID                       VARCHAR2(32)              not null
        primary key,
    ASSOCIATION_ID           VARCHAR2(32)              not null,
    MEMBER_ID                VARCHAR2(32),
    FEES                     NUMBER(10, 2),
    YEAR                     VARCHAR2(32),
    FEES_MANAGER             VARCHAR2(255),
    FEES_MANAGER_EMPLOYEE_NO VARCHAR2(8),
    REMARK                   VARCHAR2(2000),
    TYPE                     NUMBER(1),
    PAY                      NUMBER(10, 2),
    CONTENT                  VARCHAR2(2000),
    ACTIVITY                 VARCHAR2(2000),
    PEOPLE                   VARCHAR2(255),
    PAY_TIME                 DATE,
    CREATE_TIME              DATE      default SYSDATE not null,
    CREATE_USER              VARCHAR2(32),
    UPDATE_TIME              DATE      default SYSDATE not null,
    UPDATE_USER              VARCHAR2(32),
    IS_DELETED               NUMBER(1) default 0       not null
);

comment on table OMS_ASSOCIATION_FEES is '协会收支表';
comment on column OMS_ASSOCIATION_FEES.ASSOCIATION_ID is '协会ID';
comment on column OMS_ASSOCIATION_FEES.MEMBER_ID is '会员ID';
comment on column OMS_ASSOCIATION_FEES.FEES is '会费(收入金额)';
comment on column OMS_ASSOCIATION_FEES.YEAR is '年份';
comment on column OMS_ASSOCIATION_FEES.FEES_MANAGER is '会费管理人';
comment on column OMS_ASSOCIATION_FEES.REMARK is '备注';
comment on column OMS_ASSOCIATION_FEES.TYPE is '类型 0收入 1支出';
comment on column OMS_ASSOCIATION_FEES.PAY is '支出金额';
comment on column OMS_ASSOCIATION_FEES.CONTENT is '收支内容';
comment on column OMS_ASSOCIATION_FEES.ACTIVITY is '活动';
comment on column OMS_ASSOCIATION_FEES.PEOPLE is '经办人';
comment on column OMS_ASSOCIATION_FEES.PAY_TIME is '支出时间';
comment on column OMS_ASSOCIATION_FEES.CREATE_TIME is '创建时间';
comment on column OMS_ASSOCIATION_FEES.CREATE_USER is '创建人';
comment on column OMS_ASSOCIATION_FEES.UPDATE_TIME is '修改时间';
comment on column OMS_ASSOCIATION_FEES.UPDATE_USER is '修改人';
comment on column OMS_ASSOCIATION_FEES.IS_DELETED is '是否删除 0否 1是';

INSERT INTO "OMS_ASSOCIATION_FEES" VALUES ('2', '1821811368890757121', NULL, NULL, NULL, NULL, NULL, NULL, '1', '299', '聚餐', '打球', '阿曾', TO_DATE('2024-08-15 10:12:47', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-08-13 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2024-08-14 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), NULL, '0');
INSERT INTO "OMS_ASSOCIATION_FEES" VALUES ('3', '1821811368890757121', NULL, NULL, NULL, NULL, NULL, NULL, '1', '300', '聚餐', '游泳', '阿曾', TO_DATE('2024-08-14 10:12:13', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-08-13 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2024-08-14 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), NULL, '0');
INSERT INTO "OMS_ASSOCIATION_FEES" VALUES ('4', '1821811368890757121', '1822936460576243713', '100', '2024', NULL, NULL, NULL, '0', NULL, '会费收入', NULL, NULL, TO_DATE('2024-08-14 14:52:23', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-08-14 14:52:23', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2024-08-14 14:52:23', 'SYYYY-MM-DD HH24:MI:SS'), NULL, '0');
INSERT INTO "OMS_ASSOCIATION_FEES" VALUES ('1', '1821811368890757121', '1822815180767682561', '999', '2024', NULL, NULL, NULL, '0', NULL, '会费收入', NULL, NULL, TO_DATE('2024-08-13 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-08-13 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), NULL, TO_DATE('2024-08-14 10:11:42', 'SYYYY-MM-DD HH24:MI:SS'), NULL, '0');