【数据库】sqlserver mast..spt_values 视图使用案例

 

select number from master..spt_values with(nolock) where type='P'
/*解释:master..spt_values表的字段值为P的对应number字段值是从0-2047*/ 
   
  
--1.提取两个日期之间的所有月份 

declare @startDate varchar(10) 
declare @endDate varchar(10) 
select @startDate='2020-03-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 

/* 
月份 
------- 
2020-03
2020-04
2020-05
2020-06
2020-07
*/ 
--------- 
  
  
--2.求一个日期所在月份的所有日期 
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号来计算天数 

/** 
日期
-----------
2020-07-01
2020-07-02
2020-07-03
2020-07-04
2020-07-05
2020-07-06
2020-07-07
2020-07-08
2020-07-09
2020-07-10
2020-07-11
2020-07-12
2020-07-13
2020-07-14
2020-07-15
2020-07-16
2020-07-17
2020-07-18
2020-07-19
2020-07-20
2020-07-21
2020-07-22
2020-07-23
2020-07-24
2020-07-25
2020-07-26
2020-07-27
2020-07-28
2020-07-29
2020-07-30
2020-07-31
*/ 
--------- 
  
  
--3.根据给定时间为基准以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 
/** 
划分结果 
----------------------------------- 
14:10-16:10
15:10-17:10
16:10-18:10
17:10-19:10
18:10-20:10
19:10-21:10
20:10-22:10
21:10-23:10
22:10-24:10
*/ 
--------- 


--4.查询每一年
declare @num int

set @num=2020

select @num+number as 年份 from master..spt_values where type='P' 

/** 
年份
-----------
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
*/ 
---------