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-1000之间的数字

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

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

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

取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'

取两句话中重复的汉字

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

练习题：
有如下一张表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

本文地址：https://blog.csdn.net/gly1653810310/article/details/112556946

Sql Server中 master.dbo.spt_values 的用法

相关推荐