22,209
社区成员
发帖
与我相关
我的任务
分享
select sum(b.Bail) from UserTb a inner join MatchedTb b
on a.Userid=b.BUserId or a.userId=b.SUserId
where a.Userid in('22222222','11111111') and b.CreateTime between '2014/3/18' and '2014/3/25'
ALTER proc [dbo].[AgentQuery]
(
@Uaid int ,@startTime datetime ,@EndTime datetime
)
as
begin
if(isnull(@startTime,'')='')
begin
set @startTime='1900/01/01'
end
if(isnull(@EndTime,'')='')
begin
set @EndTime='2999/01/01'
end
;with cte as
(
select * ,leve=cast(1 as int),parent_pecent=cast(0 as float) from UserAuction a where not exists(select 1 from UserAuction where a.aid=uaid)
union all
select a.* ,leve=cast (1+b.leve as int),parent_pecent=a.pecentBail*isnull(nullif(b.parent_pecent,0),b.pecentBail) from UserAuction a inner join cte b on a.aid=b.uaid
), bbb as
(
select uaid,aid,AuUname,leve,pecentBail,parent_pecent,
isnull((select sum(AgentSetBail) from UserTb a inner join MatchedTb b
on a.Userid=b.BUserId or a.userId=b.SUserId
where a.AgentID =c.uaid and b.CreateTime between @startTime and @EndTime),0) as ownBail
from cte c
)
,ccc as
(
SELECT *,ISNULL((
case when exists (select 1 from bbb where aid=a.uaid) then
(SELECT SUM(ownBail) AS Bail FROM bbb b WHERE leve>=a.leve ) else
(SELECT SUM(ownBail) AS Bail FROM bbb b WHERE leve>a.leve )end
),ownBail) Sumbail FROM bbb a
) ,ddd as
(
select *,ReturnBail= isnull(nullif(parent_pecent,0),pecentBail)*Sumbail - isnull((select sum(parent_pecent*Sumbail)
from ccc where aid=a.uaid ),0) from ccc a
)
,fff as
(
select * from ddd where Uaid=@uaid
union all
select a.* from ddd a inner join fff b on a.aid=b.uaid
)
select * from fff
end
-----=========================================
ALTER proc [dbo].[AgentMatchedTb]
(
@Uaid int ,@UserName varchar(20),@startTime varchar(20) ,@EndTime varchar(20)
)
as
begin
if Object_id('tempdb..#AgentMatchedTb') is not null drop table #AgentMatchedTb
declare @sql varchar(1000)
;with cte as
(
select * ,leve=cast(1 as int),parent_pecent=cast(0 as float) from UserAuction a where uaid=@Uaid
union all
select a.* ,leve=cast (1+b.leve as int),parent_pecent=a.pecentBail*isnull(nullif(b.parent_pecent,0),b.pecentBail) from UserAuction a inner join cte b on a.aid=b.uaid
)
, bbb as
(
select distinct m.id,m.GoodsName, m.bail, m.price,m.buyMode,m.num,m.BUserId,m.SuserId,m.AgentSetBail,m.CreateTime from MatchedTb m
inner join (select UserId from UserTb a inner join cte c on a.agentid=c.uaid ) u on u.UserId=m.BUserId or u.UserId=m.SuserId
)
select * into #AgentMatchedTb from bbb
set @sql=' select * from #AgentMatchedTb where 1=1 '
if(isnull(@startTime,'')!='')
begin
set @SQL=@SQL+' and CreateTime >='''+@startTime+'''';
end
if(isnull(@EndTime,'')!='')
begin
set @SQL=@SQL+' and CreateTime <='''+@EndTime+''''
end
if(isnull(@UserName,'')!='')
begin
set @SQL=@SQL+' and SuserId ='''+ @UserName + ''' or BUserId='''+@UserName+''''
end
exec(@sql)
drop table #AgentMatchedTb
end