----先create,再alter

alter function fuc (@userid int,@strWhere varchar(max),@strWhere2 varchar(max) )  returns decimal(18,2 )

as

begin

declare @useridlist varchar(max)='',@res decimal(18,2)=0;

with cte as

(

    select ID,1 Layer from t_user  where ID=64

    union all

    select a.ID,Layer=Layer+1 from cte inner join t_user a on a.UPLineID=cte.ID  and cte.Layer<=4

)select @useridlist+=@useridlist+','+cast(ID as varchar(10)) from cte   where  cte.Layer<=3

 

declare @UserGradeDateLine varchar(max)='';

 select @UserGradeDateLine=UserGradeDateLine4 from t_user  where ID=64

    if(@UserGradeDateLine!='' and datediff(d,@UserGradeDateLine,@strWhere)>0 and datediff(d,@UserGradeDateLine,@strWhere2)>=0)

    begin

    select @res=isnull(sum(amount),0) from t_orderlucre  where CHARINDEX(','+CAST(UserID2 as varchar(10))+',',@useridlist+',')>0  and GroupSign =3  and ( datediff(mi,@UserGradeDateLine,AddTime)>0  and    datediff(d,AddTime,@strWhere2)>=0)   

    end   

    else

     begin

     select @res=isnull(sum(amount),0) from t_orderlucre  where CHARINDEX(','+CAST(UserID2 as varchar(10))+',',@useridlist+',')>0  and GroupSign =3  and ( datediff(d,@strWhere,AddTime)>0  and    datediff(d,AddTime,@strWhere2)>=0)     

     end

return @res;

end

 

--执行查询

 

  with cte as(select row_number() over(order by Amount DESC) RowID,*  from  ( select ID ,Phone ,dbo.fuc

(ID,'2018-03-28','2018-04-28') Amount from t_user)t where  1=1 )select * from cte where RowID>0 and

RowID<=15