在很多时候,我们需要计算每天的订单,每天的考勤,每个月的总销量。。。但是这些信息或许不是连续的,或许每一天有多条,有的时候某一天甚至没有数据,那么这时候我们需要一个连续的时间,然后计算每一天的收入额或者其他的信息记录。
我们一开始的设想差不多就是通过计算机能识别的方法——累加时间。就是我定义一个临时表变量,然后循环添加连续的时间到这个表变量里面去,或者字符拼接一个字段然后去进行切割。如下实现方法:
DECLARE @StartDate DATE = GETDATE() ,
@EndDate DATE= GETDATE() +7 ,
@Days INT ,--天数
@DaysList VARCHAR(MAX)= ''; --天数列表
SELECT @Days = DATEDIFF(DAY, @StartDate, @EndDate) + 1;
--遍历天数
DECLARE @_day INT= 0 ,
@i INT;
WHILE ( @_day < @Days )
BEGIN
SET @DaysList += CAST(DATEADD(DAY, @_day, @StartDate) AS VARCHAR(20))
+ ',';
SET @_day = @_day + 1;
END;
SELECT * FROM dbo.SPLIT(@DaysList, ',')
split函数的写法可以参考博客
结果如下:
那么我们仔细寻思寻思,发现这里面存在两个问题:
1.性能消耗大,临时表变量需要重复insert数据,字符拼接需要重复读写这个字段还需要切割
2.可读性差,看着不完美,要是能一个sql解决就好了
根据这些问题,我们去找解决方案。试想一下只要我们有一个连续1,2,3,4,5....等等这样的连续字段表是不是就可以获取当前时间,然后加上这个天数呢?答案是完全可以的,我们找到了sql里面的一个内置表 master..spt_values,发现里面有我们需要的连续字段type等于p的时候最大值到了2048,找到了重要信息,接下来我们去实现它
SELECT
(number+1) AS ThisDayNumber,
CONVERT (VARCHAR (10),dateadd(day, number, getdate() ),23) AS Continuity_time
FROM master..spt_values
WHERE type = 'p' AND number < 7;
结果如下:
再来练习一个,获取当前时间连续的周次,我需要周次的开始和结束时间,还有当前第几周
SELECT
(number+1) AS ThisWeekNumber,
CONVERT (VARCHAR (10),dateadd(week, number, getdate() ),23) AS Continuity_time,
CONVERT (VARCHAR (10),dateadd(week, number, getdate() ),23) AS StartTime,
CONVERT (VARCHAR (10),dateadd(week, number, getdate()+6 ),23) AS FinishTime
FROM master..spt_values
WHERE type = 'p' AND number < 4;
结果如下:
总结:发现问题,去解决它