with temp1 as (
select UserId,CreateTime,IsPopular,DisplayName,IsAgent,AgentId,ParentPath from [dbo].[C_User_Register] where (UserId='101620' or ParentPath like'/'+'101620'+'%') and IsAgent=0 and AgentId <>'101620'
)
--3存款人数/4 存款金额 --5提款人数/6提款金额 --7红利人数/8 红利金额 --9返水人数/10 返水金额
select * from [S_Member_Fund_Static] t ,temp1 t2
where t.UserId=t2.UserId and t.StatisDate >= '2023-07-01 00:00:00' and t.StatisDate < ='2023-07-31 23:59:59' and t2.AgentId='101622'
实例：
with temp2 as (
select UserId,CreateTime,IsPopular,DisplayName,IsAgent,AgentId,ParentPath from [dbo].[C_User_Register] where (UserId='101620' or ParentPath like'/'+'101620'+'%') and IsAgent=0 and AgentId <>'101620'
)
--3存款人数/4 存款金额 --5提款人数/6提款金额 --7红利人数/8 红利金额 --9返水人数/10 返水金额
select AgentId,
case when SUM(case when t.FillMoneyCount>0 then 1 else 0 end )-COUNT(distinct t.UserId)>=0 then COUNT(distinct t.UserId) else SUM(case when t.FillMoneyCount>0 then 1 else 0 end ) end ,
case when sum(case when t.WithdrawCount>0 then 1 else 0 end )-COUNT(distinct t.UserId)>=0 then COUNT(distinct t.UserId) else sum(case when t.WithdrawCount>0 then 1 else 0 end )end,
case when sum(case when t.BonusCount>0 then 1 else 0 end )-COUNT(distinct t.UserId)>=0 then COUNT(distinct t.UserId) else sum(case when t.BonusCount>0 then 1 else 0 end )end ,
case when sum(case when t.BackWaterCount>0 then 1 else 0 end ) -COUNT(distinct t.UserId)>=0 then COUNT(distinct t.UserId) else sum(case when t.BackWaterCount>0 then 1 else 0 end )end
from [S_Member_Fund_Static] t ,temp2 t2
where t.UserId=t2.UserId and t.StatisDate >= '2023-07-01 00:00:00' and t.StatisDate < ='2023-07-31 23:59:59'
group by AgentId
