Sql Server中 master.dbo.spt_values 的用法

master.dbo.spt_values是一个数据库常量表，表里都是一些枚举数据。

我们可以先查询一下看表里都有什么☞

select * from master.dbo.spt_values

查询得知表里有五个字段：☞
name（名称）,number（值）,type（类型）,low（下限）,high（上限）,status（状态）

抽取一个type看下☞

select number from master..spt_values where type = 'p' 
--Type = 'P'的number数字范围是0-2047

---

1. 取1-1000之间的数字

select number from master..spt_values 
where type='P' and number between 1 and 1000

2. 取当前日期往后的365个日期

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

3. 取2019年4月至2020年3月的月份

select convert(varchar(6),dateadd(month,number,'20190401'),112) as month
from master..spt_values
where type = 'P' and dateadd(month,number,'20190401') <= '20200301'

4. 取两句话中重复的汉字

declare @text1 varchar(100),@text2 varchar(100)
set @text1 = '没有理想的人不伤心' 
set @text2 = '他也会伤心'
SELECT SUBSTRING(@text2,number,1) as value
from master..spt_values
where type='p' and number <= LEN(@text2) and CHARINDEX(SUBSTRING(@text2,number,1),@text1) > 0

5. 求一个日期所在月份的所有日期

declare @date datetime
set @date = '20220225'
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')

---

练习题：
有如下一张表T

ITEM表示物料种类，QTY表示发货数量，MIN_QTY表示最小发货量，希望通过SQL查询得出下面的结果：

解释：通过MIN_QTY将QTY进行等分，不能够完全等分的，剩余部分用QTY减去等分后的数量。例如230的最小数量是80，可以先等分成2份，剩下部分用230-2*80=70

提示：可以使用MASTER.DBO.SPT_VALUES进行关联后进行等分（SQL Server的解法）

select Item,QTY,MIN_QTY,SEQ
,case when MIN_QTY * rnk < QTY THEN MIN_QTY ELSE QTY-MIN_QTY*(rnk-1) END AS NEW_QTY
from (
select Item,QTY,MIN_QTY,count(MIN_QTY) over ( partition by Item ) as SEQ,b.number + 1 as rnk
from T a
CROSS join master..spt_values b
where b.number * a.MIN_QTY <= a.qty and b.Type = 'P'
) a