master.dbo.spt_values

sql开发中经常需要使用数字或者时间的常量表。

比如，输出一年的月份表，输出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)
)
GO
DECLARE 
  @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 Mon

drop TABLE Mon

1/25/50/100美分，多少种可能拼凑成2美元

 1/25/50/100美分，多少种可能拼凑成2美元

;WITH CTE1 AS 
	(
	SELECT number,number*1 AS no1
		FROM master..spt_values
			WHERE type='P'
			AND number <201
	),
	CTE2 AS 
	(
	SELECT number,number*25 AS no2
		FROM master..spt_values
			WHERE type='P'
			AND number*25<201
	),
	CTE3 AS 
	(
	SELECT number,number*50 AS no3
		FROM master..spt_values
			WHERE type='P'
			AND number*50<201
	),
	CTE4 AS 
	(
	SELECT number,number*100 AS no4
		FROM master..spt_values
			WHERE type='P'
			AND number*100<201
	)
	SELECT CTE1.number,
			CTE2.number,
			CTE3.number,
			CTE4.number
	FROM CTE1
	CROSS JOIN CTE2
	CROSS JOIN CTE3
	CROSS JOIN CTE4
	WHERE CTE1.no1+CTE2.no2+CTE3.no3+CTE4.no4=200
	ORDER BY 1 DESC,2 DESC,3 DESC,4 DESC