SQL语句创建函数

本文介绍了一个复杂的SQL查询案例,该查询使用递归公用表表达式(CTE)来获取特定用户及其下级用户(最多四层)的ID列表,并根据日期范围及用户等级有效期筛选订单金额总和。

----先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

转载于:https://www.cnblogs.com/weimingxin/p/8900499.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值