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