巧用master..spt_values表输出数字或者时间常量表2009-10-16 14:32sql开发中经常需要使用数字或者时间的常量表。比如,输出一年的月份表,输出1000以内的自然数等等。数量连续且不超过2048。那么使用master..spt_values表就会再也方便不过了。例如输出1000以内的自然数:select number from master..spt_values where type='P' and number between 1 and 1000输出2008年至今以来的月份列表:create table Mon ( ID int identity(1,1), Mon varchar(6) ) GODECLARE @BeginMonth varchar(6), @EndMonth varchar(6) SELECT @BeginMonth='200801', @EndMonth='200906'INSERT Mon(Mon) SELECT CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112) FROM master..spt_values WHERE type='P' and DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01' SELECT * FROM MonDROP TABLE Mon
系统表而已,SELECT D ATEADD(DD,NUBMER,'2009-10-01') AS TIME FROM MASTER..SPT_VALUES WHERE TYPE='P' AND CONVERT(VARCHAR(10),DATEADD(DD,NUBMER,'2009-10-01'),120)<'2009-10-31'
where type='P' and
number between 1 and 1000输出2008年至今以来的月份列表:create table Mon
(
ID int identity(1,1),
Mon varchar(6)
)
GODECLARE
@BeginMonth varchar(6),
@EndMonth varchar(6)
SELECT
@BeginMonth='200801',
@EndMonth='200906'INSERT Mon(Mon)
SELECT
CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112)
FROM
master..spt_values
WHERE
type='P'
and
DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01'
SELECT * FROM MonDROP TABLE Mon
ATEADD(DD,NUBMER,'2009-10-01') AS TIME
FROM
MASTER..SPT_VALUES
WHERE TYPE='P'
AND
CONVERT(VARCHAR(10),DATEADD(DD,NUBMER,'2009-10-01'),120)<'2009-10-31'