自定义函数中返回表的例子

最新推荐文章于 2024-01-23 10:50:26 发布

lovejesuslove

最新推荐文章于 2024-01-23 10:50:26 发布

阅读量471

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/lovejesuslove/article/details/50943856

版权

该博客介绍了如何使用SQL自定义函数来查询不同日期的逾期量，包括今天、下周的工作日数据。函数根据输入日期判断是星期一、星期二还是星期五，然后查询相应的逾期量数据并返回到一个表格中。

摘要由CSDN通过智能技术生成

USE [作业数据库]
GO

SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[FK_baogaof1](@time datetime) -- drop function FK_baogaof1
returns @mytable TABLE(委托报告量 varchar(100),CECR1 int,CECR11 int,CECR3 int,CECR16 int,CECR6 int,CECR7 int,OTHERS int,总量 int)
as
begin

declare @CECR1Num int
declare @CECR11Num int
declare @CECR3Num int
declare @CECR16 int
declare @CECR6 int
declare @CECR7 int
declare @OtherNum int
declare @total int
declare @time1 datetime
set @time1=null

--------------如果是星期一，查询：逾期量今天星期二三四五和下星期一。
if datename(weekday, @time)= '星期一'
begin
-----------------------逾期量--------------------------------------------------------------------------------------------
select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',1)
select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',1)
select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',1)
select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',1)
select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',1)
select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',1)
select @OtherNum =dbo.FK_baogaof2(@time,'其他',1)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select '逾期量',@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

--今天
select @CECR1Num=dbo.FK_baogaof2(@time,'CECR1',0)
select @CECR11Num =dbo.FK_baogaof2(@time,'CECR1-1',0)
select @CECR3Num =dbo.FK_baogaof2(@time,'CECR3',0)
select @CECR16 =dbo.FK_baogaof2(@time,'CECR16',0)
select @CECR6 =dbo.FK_baogaof2(@time,'CECR6',0)
select @CECR7 =dbo.FK_baogaof2(@time,'CECR7',0)
select @OtherNum =dbo.FK_baogaof2(@time,'其他',0)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select CONVERT(varchar(100), @time, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

set @time1=dateadd(day, 1, @time) --星期二
select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

set @time1=dateadd(day, 2, @time) --星期三
select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

set @time1=dateadd(day, 3, @time) --星期四
select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

set @time1=dateadd(day, 4, @time) --星期五
select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total

set @time1=dateadd(day, 7, @time) --下周一
select @CECR1Num=dbo.FK_baogaof2(@time1,'CECR1',0)
select @CECR11Num =dbo.FK_baogaof2(@time1,'CECR1-1',0)
select @CECR3Num =dbo.FK_baogaof2(@time1,'CECR3',0)
select @CECR16 =dbo.FK_baogaof2(@time1,'CECR16',0)
select @CECR6 =dbo.FK_baogaof2(@time1,'CECR6',0)
select @CECR7 =dbo.FK_baogaof2(@time1,'CECR7',0)
select @OtherNum =dbo.FK_baogaof2(@time1,'其他',0)
set @total=@CECR1Num+@CECR11Num+@CECR3Num+@CECR16+@CECR6+@CECR7+@OtherNum
insert @mytable select CONVERT(varchar(100), @time1, 23),@CECR1Num,@CECR11Num,@CECR3Num,@CECR16,@CECR6,@CECR7,@OtherNum,@total