SQL语句创建函数

最新推荐文章于 2024-02-13 00:45:51 发布

转载最新推荐文章于 2024-02-13 00:45:51 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/weimingxin/p/8900499.html

文章标签：

#数据库

本文介绍了一个复杂的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