sql 杂

/* row_number() over ([partition by t.xx] order by t.kk /asc/desc) rn */

select * from (
select t1.*,
row_number() over (partition by t1.Name order by t1.CreateDate desc) rn 
from t1
inner join t2 on ...
) XX

where XX.rn=1

select t1.Name,max(t1.Number) from ta group by t1.Name

select p.loanid,
                   p.customerid,
                   sum(p.totalfee) totalfee, --应收本息
                   sum(case when p.paystate='01' then p.totalfee else 0 end) unbacktotal, --待收本息
                   sum(case when p.paystate='02' then p.totalfee else 0 end) backedtotal,  --已收本息
                   max(case when p.paystate='02' then p.ordernumber else null end) backedtimes --已还期数
              from t_profitplan p 
             group by p.loanid,p.customerid

SqlServer
select top(1) t1.* from t1


Oracle
select * from (
select * from t1 order by t1.CreateDate desc
) where rownum=1

insert into t_customermsg(id,customerid,content)
    select sys_guid(),c.id,'哈喝' from t_customer c
     inner join t_loginaccount l on c.id=l.id and l.emailvalid='02'
     where c.status='01';


/*
  declare 
  cursor c is  select * from t_customer t1
inner join t_loginaccount t2 on t1.id=t2.id and t2.emailvalid='02'
where t1.status='01';
  c_row c%rowtype;
  
  newcontent nvarchar2(1024);

begin
  newcontent='hello';
  for c_row in c loop
    --dbms_output.put_line(c_row.id||'*_*'||c_row.customername);
    insert into t_customermsg(id,customerid,content) values (sys_guid(),c_row.id,'哈喝');
  end loop;
end;
*/

    update t_LotteryOpp lo set (lo.Amount,lo.Lastupdatedate)=
       (select t.Amount,sysdate
          from t_Temp t
         where t.CustomerId=lo.CustomerId 

        );

create or replace package PCK_Lottery is

  -- Author  : licheng
  -- Created : 2015-1-4
  -- Purpose : 抽奖活动

  -- Public type declarations
  type out_cursor is ref cursor;

  --我的推荐
  procedure pro_GetMyRecommendList(p_CustomerId varchar2,
                                   p_PromotionId varchar2,
                                   op_Result out out_cursor);

  --抽奖机会
  procedure pro_RefreshLotteryOpp(p_PromotionId varchar2);

end PCK_Lottery;

create or replace package body PCK_Lottery is

--我的推荐
  procedure pro_GetMyRecommendList(p_CustomerId varchar2,
                                   --p_StartDate date,
                                   --p_EndDate date,
                                   p_PromotionId varchar2,
                                   op_Result out out_cursor) AS
  v_StartDate date;
  v_EndDate date;
  v_EndDateStr varchar2(50);
  begin
    
    select t.StartDate,t.EndDate into v_StartDate,v_EndDate from t_PromotionInfo t where t.Id=p_PromotionId; --and t.Status='01';
    v_EndDateStr:=to_char(v_EndDate,'yyyy-MM-DD')||' 23:59:59';
    v_EndDate:=to_date(v_EndDateStr,'yyyy-MM-dd HH24:MI:ss');
    
    open op_Result for
      select TT.* from (
        select la.NickName,c.Mobile,ptr.TransAmt TenderMoney,
               row_number() over(partition by ptr.CustomerId order by ptr.CreateDate asc) rn
        from T_LoginAccount t
        inner join T_LoginAccount la on la.Referee=t.RefereeCode
        inner join T_Customer c on c.Id=la.Id
        inner join T_PnrTenderRecords ptr on ptr.CustomerId=la.Id
        inner join T_TenderRecord tr on tr.PnrTenderId=ptr.Id
        where t.Status='01' and t.Id=p_CustomerId
        and la.Status='01'
        --and la.CreateDate>=to_date('2015-01-01 00:00:00', 'yyyy-MM-dd HH24:MI:ss')
        --and la.CreateDate<=to_date('2015-01-31 23:59:59','yyyy-MM-dd HH24:MI:ss')
        and la.CreateDate>=v_StartDate and la.CreateDate<=v_EndDate
        and ptr.Status='01'
        and (ptr.BehindIsSuccess='Y' or ptr.PageIsSuccess='Y')
        and ptr.CreateDate>=v_StartDate and ptr.CreateDate<=v_EndDate
        and tr.IsFreezeSuccess='02'
        and tr.UserTypeId is null and tr.UserAccountId is null
      ) TT
      where TT.rn=1 and TT.TenderMoney>=1000;
      
  end pro_GetMyRecommendList;

--抽奖机会
  procedure pro_RefreshLotteryOpp(p_PromotionId varchar2) IS
    v_PromotionExisted number;
    v_StartDate date;
    v_EndDate date;
    v_EndDateStr varchar2(50); 
    
    --查询每个用户推荐的有效的人数，即抽奖机会
    cursor c_result is 
       select TT.CustomerId,count(*) Amount from (
          select t.Id CustomerId,ptr.TransAmt TenderMoney,
                 row_number() over(partition by ptr.CustomerId order by ptr.CreateDate asc) rn
          from T_LoginAccount t
          inner join T_LoginAccount la on la.Referee=t.RefereeCode
          inner join T_Customer c on c.Id=la.Id
          inner join T_PnrTenderRecords ptr on ptr.CustomerId=la.Id
          inner join T_TenderRecord tr on tr.PnrTenderId=ptr.Id
          where t.Status='01'
          and la.Status='01'
          and la.CreateDate>=v_StartDate and la.CreateDate<=v_EndDate
          and ptr.Status='01'
          and (ptr.BehindIsSuccess='Y' or ptr.PageIsSuccess='Y')
          and ptr.CreateDate>=v_StartDate and ptr.CreateDate<=v_EndDate
          and tr.IsFreezeSuccess='02'
          and tr.UserTypeId is null and tr.UserAccountId is null
        ) TT
        where TT.rn=1 and TT.TenderMoney>=1000
        group by TT.CustomerId;
     
     c_row c_result%rowtype;
     v_Existed number;
     v_Guid varchar2(36);
     
  begin
    
    --若活动实效，则不执行
    select count(*) into v_PromotionExisted from dual t where exists
       (select * from t_PromotionInfo t where t.Id=p_PromotionId and t.Status='01');
    if v_PromotionExisted=0 then
      return;
    end if;
    
    --查询活动起止时间
    select t.StartDate,t.EndDate into v_StartDate,v_EndDate from t_PromotionInfo t where t.Id=p_PromotionId and t.Status='01';
    v_EndDateStr:=to_char(v_EndDate,'yyyy-MM-DD')||' 23:59:59';
    v_EndDate:=to_date(v_EndDateStr,'yyyy-MM-dd HH24:MI:ss');
    
    --若活动未开始或已结束，则不执行
    if sysdate<v_StartDate or sysdate>v_EndDate then
      return;
    end if;
    
    for c_row in c_result loop
      --若T_LotteryOpp有数据，则update
      select count(*) into v_Existed from dual where exists
         ( select * from t_LotteryOpp t where t.CustomerId=c_row.CustomerId );
      if v_Existed!=0 then
        update t_LotteryOpp 
           set Amount=c_row.Amount,LastUpdateDate=sysdate 
         where CustomerId=c_row.CustomerId 
           and PromotionId=p_PromotionId 
           and Amount!=c_row.Amount;
      --若T_LotteryOpp有数据，则insert
      else
        select sys_guid() into v_Guid from dual;
        v_Guid:=substr(v_Guid,1,8)||'-'||substr(v_Guid,9,4)||'-'||substr(v_Guid,13,4)||'-'||substr(v_Guid,17,4)||'-'||substr(v_Guid,20,12);
        insert into t_Lotteryopp values(
           v_Guid,
           c_row.CustomerId,
           p_PromotionId,
           c_row.Amount,
           0,
           v_StartDate,
           v_EndDate,
           '01',
           sysdate,
           null,
           sysdate,
           null);
      end if;
    end loop;
    
    /*
    update t_LotteryOpp lo set (lo.Amount,lo.Lastupdatedate)=
       (select c_result.Amount,sysdate
          from c_result 
         where c_result.CustomerId=lo.CustomerId 
           and lo.LotteryId=p_PromotionId
           and c_result.Amount!=lo.Amount
        );
        
     insert into t_LotteryOpp(Id,
                              CustomerId,
                              PromotionId,
                              Amount,
                              UsedAmount,
                              StartDate,
                              EndDate,
                              Status,
                              CreateDate,
                              Creator,
                              LastUpdateDate,
                              LastUpdateUser
                              )
        select substr(g.Guid,1,8)||'-'||substr(g.Guid,9,4)||'-'||substr(g.Guid,13,4)||'-'||substr(g.Guid,17,4)||'-'||substr(g.Guid,20,12),
               c_result.CustomerId,
               p_PromotionId,
               c_result.Amount,
               0,
               v_StartDate,
               v_EndDate,
               '01',
               sysdate,
               null,
               sysdate,
               null
          from c,
               (select sys_guid() Guid from dual) g
         where not exists
           ( select * from t_LotteryOpp t where t.Customerid=c_result.CustomerId and t.PromotionId=p_PromotionId );
    */
    commit;
    
  end pro_RefreshLotteryOpp;

end PCK_Lottery;