正文

SqlServer使用表值函数汇总

IP云
IP云V管理员/10 阅读
08/27
提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

先谈谈需求，我们先创建一张表，脚本如下：

create table Cost
(
Id int identity(1,1) primary key,--编号
CostTime date,--时间
Num int--销售额
);
insert into Cost(CostTime,Num) values('2016-09-01','');
insert into Cost(CostTime,Num) values('2016-09-01','');
insert into Cost(CostTime,Num) values('2016-09-03','');
insert into Cost(CostTime,Num) values('2016-09-05','');

如果我们要统计上面的这张表在每天的销售额，可以按照CostTime分组，然后用sum(Num)进行统计，sql如下：

select CostTime,sum(Num) Num from Cost where CostTime>='2016-09-01' and CostTime<='2016-09-05' group by CostTime;

执行结果如下：

很明显只有3天的数据，如果我们想要把2号和4号的数据也显示出来呢，期望结果如下：

====================================================================================

很明显要实现上面的需求我们首先考虑的是创建一张临时表来存放2016-09-01到2016-09-05这个区间的数据，然后通过汇总关联查询即可，可是如果创建临时表对系统的开销还是比较大的，有没有什么方法可以不用创建表而实现该需求呢，答案是：表值函数

我们首先创建表值函数如下：

CREATE FUNCTION [dbo].[GetTimeCol]
(
@beginTime date,
@endTime date
)
RETURNS @returntable TABLE
(
CostTime date
)
AS
BEGIN
while(@beginTime<=@endTime)
begin
insert into @returntable select @beginTime
select @beginTime=dateadd(day,1,@beginTime)
end;
RETURN
END

然后执行最终sql语句如下：

select a.CostTime,isnull(b.Num,0) Num from GetTimeCol('2016-09-01','2016-09-05') a left join
(select CostTime,sum(Num) Num from Cost where CostTime>='2016-09-01' and CostTime<='2016-09-05' group by CostTime) b
on a.CostTime=b.CostTime

OK，就得到我们希望的结果了

涛哥理解：SqlServer对表值函数支持挺好，但是不知道Oracle、MySql是否支持