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
select top(1) t1.* from t1
Oracle
select * from (
select * from t1 order by t1.CreateDate desc
) where rownum=1
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;
*/
(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;