create table "ONLINE1"."INTENTION_INVEST_INFO"(

        "INTENTION_INVEST_ID" NUMBER not null,

       "INTENTION_LOAN_ID" NUMBER not null,

       "USER_ID" NUMBER not null,

       "INTENTION_AMOUNT" NUMBER(22,7) not null,

       "UNINVEST_AMOUNT" NUMBER(22,7),

       "HAVA_SCALE" NUMBER(22,18) not null,

       "INVEST_TIME" DATE not null,

       "STATUS" VARCHAR2(2),

       "DESCRIPTION" VARCHAR2(200),

       "UPDATE_TIME" DATE,

        constraint "PK_INTENTION_INVEST_INFO" primary key ("INTENTION_INVEST_ID")

    );

    comment on table "ONLINE1"."INTENTION_INVEST_INFO" is '理财客户意向投资资金记录表';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."INTENTION_INVEST_ID" is 'ID';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."INTENTION_LOAN_ID" is '意向标ID，相同期数的ID相同';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."USER_ID" is '用户ID';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."INTENTION_AMOUNT" is '意向投标总金额';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."UNINVEST_AMOUNT" is '剩余未投标金额';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."HAVA_SCALE" is '占比';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."INVEST_TIME" is '投标时间';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."STATUS" is '状态：1.投标中2.已投完';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."DESCRIPTION" is '描述';

    comment on column "ONLINE1"."INTENTION_INVEST_INFO"."UPDATE_TIME" is '更新时间';



    create unique index "PK_INTENTION_INVEST_INFO" on "ONLINE1"."INTENTION_INVEST_INFO"("INTENTION_INVEST_ID");



要求查询结果：未投完并且只显示每期的前20个用户，按照时间倒叙

SELECT * FROM

(SELECT T.*,

       ROW_NUMBER() OVER(PARTITION BY T.INTENTION_LOAN_ID ORDER BY T.INVEST_TIME DESC) RN

  FROM INTENTION_INVEST_INFO T

)

WHERE RN<=20

ORDER BY INTENTION_LOAN_ID,INVEST_TIME DESC;