27,579
社区成员
发帖
与我相关
我的任务
分享
Alter PROCEDURE [dbo].[statisticsMoneyInfo]
@DTime datetime, --时间
@DTimeEnd datetime, --时间
@PSTypeID int,
@iPageSize int --页面大小
,@iIndexPage int --多少页
,@xiadan money output --下单
,@cedan money output --撤单
,@jiangli1 money output --奖励
,@jiangli2 money output --奖励2
AS
BEGIN
Select UserID
,row_number() over (order by userId asc) as rowNum --行号,ID排序
into #temp100 --存入临时表
from UserInfo where AId=0
select UserID as userid into #userIdTable from #temp100 where rowNum between @iPageSize*(@iIndexPage-1)+1 and (@iPageSize*@iIndexPage)--查询要统计的顶级代理UserId
--#userIdTable所有的要统计的UserId 都在这个里面。下面是对每个userid统计,不知道不用循环怎么统计,
DECLARE @AgentId int; --
set @AgentId=0
SELECT @AgentId = count(*) FROM #userIdTable
while @AgentId>0
begin
select
@AgentId as 用户ID,
sum(case ChangeMoneyInfo.ChangeTypeID when 1 then changeMoney else 0 end) as 下单,
sum(case ChangeMoneyInfo.ChangeTypeID when 3 then changeMoney else 0 end) as 撤单,
sum(case ChangeMoneyInfo.ChangeTypeID when 2 then changeMoney else 0 end) as 奖励,
sum(case ChangeMoneyInfo.ChangeTypeID when 4 then changeMoney else 0 end) as 奖励2
into #temp --存入临时表
from dbo.ChangeMoneyInfo
where 1=1
and PSTypeID=isnull(@PSTypeID,PSTypeID)
and (ChangeMoneyInfo.userId in ((select UserID from Userinfo where AllAgentId like '%|'+cast(@AgentId as varchar(10))+'|%' or UserID=@AgentId)))--团队会员)
and ChangeTime>=Isnull(@DTime,ChangeTime)
and ChangeTime<=Isnull(@DTimeEnd,ChangeTime)
delete from #userIdTable where userid=@AgentId
end
drop table #userIdTable drop table #temp
END
insert into #temp
select
@AgentId as 用户ID,
sum(case ChangeMoneyInfo.ChangeTypeID when 1 then changeMoney else 0 end) as 下单,
sum(case ChangeMoneyInfo.ChangeTypeID when 3 then changeMoney else 0 end) as 撤单,
sum(case ChangeMoneyInfo.ChangeTypeID when 2 then changeMoney else 0 end) as 奖励,
sum(case ChangeMoneyInfo.ChangeTypeID when 4 then changeMoney else 0 end) as 奖励2
from dbo.ChangeMoneyInfo
where 1=1
and PSTypeID=isnull(@PSTypeID,PSTypeID)
and (ChangeMoneyInfo.userId in (select UserID from Userinfo where AllAgentId like '%|'+cast(@AgentId as varchar(10))+'|%') or UserID=@AgentId)--团队会员)
and ChangeTime>=Isnull(@DTime,ChangeTime)
and ChangeTime<=Isnull(@DTimeEnd,ChangeTime)
group by Userid
有个问题是要是没有数据的时候,就没添加。。。
Alter PROCEDURE [dbo].[statisticsMoneyInfo]
@DTime datetime, --时间
@DTimeEnd datetime, --时间
@PSTypeID int,
@iPageSize int --页面大小
,@iIndexPage int --多少页
,@xiadan money output --下单
,@cedan money output --撤单
,@cunkuan money output --存款
,@zhongjiang money output --中奖
,@fandian money output --返点
AS
BEGIN
Select UserID
,row_number() over (order by userId asc) as rowNum --行号,ID排序
into #temp100
from UserInfo where AgentId=0
select UserID as userid into #userIdTable from #temp100 where rowNum between @iPageSize*(@iIndexPage-1)+1 and (@iPageSize*@iIndexPage)--查询要统计的顶级代理UserId
DECLARE @AgentId int; --
DECLARE @UserCount int; --
SELECT @UserCount = count(*) FROM #userIdTable
CREATE TABLE #temp (用户ID INT,下单 INT,撤单 INT ,奖励 INT,奖励2 INT )
while (@UserCount>0)
begin
set @AgentId=0
set @AgentId = (select top 1 userid from #userIdTable)
insert into #temp
select
@AgentId as 用户ID,
sum(case ChangeMoneyInfo.ChangeTypeID when 1 then changeMoney else 0 end) as 下单,
sum(case ChangeMoneyInfo.ChangeTypeID when 3 then changeMoney else 0 end) as 撤单,
sum(case ChangeMoneyInfo.ChangeTypeID when 2 then changeMoney else 0 end) as 奖励,
sum(case ChangeMoneyInfo.ChangeTypeID when 4 then changeMoney else 0 end) as 奖励2
from dbo.ChangeMoneyInfo
where 1=1
and PSTypeID=isnull(@PSTypeID,PSTypeID)
and (ChangeMoneyInfo.userId in (select UserID from Userinfo where AllAgentId like '%|'+cast(@AgentId as varchar(10))+'|%') or UserID=@AgentId)--团队会员)
and ChangeTime>=Isnull(@DTime,ChangeTime)
and ChangeTime<=Isnull(@DTimeEnd,ChangeTime)
group by Userid
delete from #userIdTable where userid=@AgentId
set @UserCount = @UserCount-1;
end
--添加时间-升
select * from #temp
drop table #userIdTable
drop table #temp
drop table #temp100
END