SQLServer： 构造辅助数据 MASTER..spt_values

1、系统常量表（顺序号0-2047）

SELECT number 
  FROM MASTER..spt_values 
   WHERE  TYPE = 'p'

/*解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/ 

2、今天是之后面连续日期（2048条）

SELECT CONVERT(CHAR(10), DATEADD(DAY, number, GETDATE()), 120) AS [日期]
     FROM MASTER..spt_values

      WHERE TYPE='P' AND number>0

3、--提取两个日期之间的所有月份 
 
declare @startDate varchar(10) 
declare @endDate varchar(10) 
select @startDate='2023-01-01',@endDate=GETDATE() 
select convert(varchar(7),dateadd(mm,number,@startDate),120) as [月份] 
from master..spt_values with(nolock) 
where type='P' and number>=0 
and dateadd(mm,number,@startDate)<=@endDate 

 

4、--求一个日期所在月份的所有日期 

declare @date datetime 
set @date=GETDATE()
select convert(char(7),@date,120)+'-'+right('0'+convert(varchar(2),number),2) as 日期
from master..spt_values with(nolock) 
where type='P'

     and number>=1 
     and number<=datediff(dd,convert(char(7),@date,120)+'-01',convert(char(7),dateadd(mm,1,@date),120)+'-01')--转换为1号来计算天数 

5、--根据给定时间为基准以2小时为划分，得出一天划分出的时间段 
declare @time varchar(5) 
set @time='14:10'
select ltrim(a.number)+right(@time,3)+'-'+ltrim(b.number)+right(@time,3) as 划分结果
from master..spt_values a with(nolock),master..spt_values b with(nolock) 
where a.type='P' and b.type='P'
and a.number>=left(@time,2) and b.number<=24 
and a.number+2=b.number 

 

6、 --查询每一年
declare @num int 
set @num=2023 
select @num+number as 年份 
from master..spt_values 
where type='P' 

 7、取1-1000之间的数字

select number

from master..spt_values

where type='P' and

        number between 1 and 1000

8、取当前日期往后的365个日期

select convert(varchar(10), dateadd(day,number,getdate()), 120) as [日期]  
from master..spt_values
where type = 'P' and number between 0 and 365  
 

9、取2022年4月至2023年3月的月份

select convert(varchar(6),dateadd(month,number,'20220401'),112) as month

from master..spt_values

where type = 'P' and dateadd(month,number,'20230401') <= '20200301' 

10、求一个日期所在月份的所有日期

declare @date datetime
set @date = '20230225'
select convert(char(7),@date,120) + '-' + right('0' + convert(varchar(2),number),2) as [日期格式1]
,ltrim(year(@date)) + right(100+month(@date),2)+right('0'+ltrim(number),2) as [日期格式2]
from master..spt_values
where type = 'p'
and number >= 1
and number <= datediff(dd,convert(char(7),@date,120) + '-01'
    ,convert(char(7),dateadd(mm,1,@date),120)+'-01')

11、**当月所有日期+星期***

SELECT CONVERT
        (
            VARCHAR ( 10 ),
            dateadd(
                dd,
                number,
                dateadd(
                    dd,- DAY ( GETDATE() ) + 1,GETDATE()  
                )),
            120 
        ) AS every_time,--日期
        datepart(
            weekday,
            dateadd(
                dd,
                number,
                dateadd(
                    dd,- DAY ( GETDATE()  ) + 1,GETDATE()  
                )) 
        ) AS Weekd --星期几
        
    FROM
        master..spt_values n   ---   master..spt_values   用来构造辅助数据
    WHERE
        n.type = 'p' 
        AND number < datediff(
            DAY,
            dateadd(
                dd,- DAY ( GETDATE()  ) + 1,GETDATE()  
            ),
            dateadd(
                mm,+ 1,
                dateadd(
                    dd,- DAY ( GETDATE()  ) + 1,GETDATE()  
                ))) 

 

----===========

98、

--统计某月份周情况 没有的补0
if object_id('temp_tb') is not null 
drop table temp_tb

create table temp_tb
([ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[test_values] int NULL,
[time] datetime null,
);
go

insert into temp_tb([test_values],[time]) 
select 3,'2009-2-1 10:12:30' union all
select 5,'2009-2-5 09:20:23' union all
select 6,'2009-2-6 11:21:34' union all
select 7,'2009-2-8 12:22:12' union all
select 2,'2009-2-10 16:45:25' union all
select 3,'2009-2-13 13:21:14' union all
select 5,'2009-2-15 15:58:09' union all
select 1,'2009-2-15 08:35:47' union all
select 1,'2009-2-15 09:13:07' union all
select 9,'2009-2-15 09:15:04'

select * from temp_tb

SELECT
    A.[Week],
    ISNULL(COUNT(B.test_values),0) AS num
FROM (
    SELECT
        datepart(week,dateadd(week,0,dateadd(day,number,'2009-02-01'))) AS [Week]
    FROM master.dbo.spt_values 
    WHERE type='p' AND number BETWEEN 0 AND 27
    GROUP BY datepart(week,dateadd(week,0,dateadd(day,number,'2009-02-01')))
) AS A
LEFT JOIN temp_tb AS B
    ON A.[Week]=DATEPART(week,time)
GROUP BY A.[Week]

SELECT DATEPART(WEEK,'2009-10-30 12:15:32.1234567 +05:10')  --44周
 

99、等分数

WITH cte(past) AS(
         SELECT 2.2
     )
SELECT number,
       (
           CASE 
                WHEN past >= number THEN 1.0      --当原小数值>当前行值显示 1.0
                ELSE past -FLOOR(past)              --否则显示为小数值
           END
       ) AS result
FROM   cte,
       MASTER..spt_values
WHERE  
       1=1
       AND CEILING(past) >= number    --原小数值向上取大 >= number
       AND TYPE = 'P'
       AND number > 0