求sql，未投完并且只显示每期的前20个用户

Question

    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个用户，按照时间倒叙

Answer 1

借个楼问个问题
这是从SQLSERVER上移到ORACLE上，运行的时候提示命令未正确结束，ORACLE中有没有value()方法，没有的话怎么改下，高手给弄下，谢谢，没有分了，都问问题花完了，谅解下create or replace procedure SP_CREATE_INDEX
(
KeyFieldNo varchar2,
KeySubFieldNo varchar2,
TitleFieldNo varchar2,
TitleSubFieldNo varchar2,
TypeFieldNo varchar2,
TypeSubFieldNo varchar2,
DocTypeCode varchar2
)
as
KeyField varchar2(500);TitleField varchar2(500);TypeField varchar2(500);Sql1 varchar2(5000);
begin
KeyField:= '(/Marc/Record/DataFields/DataField[@Tag='''''||KeyFieldNo||''''']/SubDataFields/SubDataField[@code='''''||KeySubFieldNo||'''''])[1]';
TitleField:= '(/Marc/Record/DataFields/DataField[@Tag='''''||TitleFieldNo||''''']/SubDataFields/SubDataField[@code='''''|| TitleSubFieldNo||'''''])[1]';
TypeField:= '(/Marc/Record/DataFields/DataField[@Tag='''''|| TypeFieldNo||''''']/SubDataFields/SubDataField[@code='''''|| TypeSubFieldNo||'''''])[1]';
Sql1:= 'Update Nbmm_Marc set Nbmm_Marc.keyfield=nvl(Nbmm_Marc_Data.MarcContent.value('''||KeyField ||''',''nvarchar2(max)''),''''),Nbmm_Marc.titlefield=nvl(Nbmm_Marc_Data.MarcContent.value('''||TitleField||''', ''nvarchar(max)''), ''''), Nbmm_Marc.typefield=nvl(Nbmm_Marc_Data.MarcContent.value('''||TypeField||''', ''nvarchar(max)''), '''') from Nbmm_Marc_Data, Nbmm_Marc where '||'Nbmm_Marc.MarcIdentity=Nbmm_Marc_Data.MarcIdentity and Nbmm_Marc.DocTypeCode='''||DocTypeCode||'''';
/*dbms_output.put_line(Sql1); */
execute immediate Sql1;
end;

Answer 2

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;

Answer 3

 SQLSERVER的存储过程,有点纠结,这个看不太懂,写出需求,重新写吧...