master..spt_values

master..spt_values是要导出数据的表，spt_values是在master数据库下，所以是master..spt_values。

用法举例

获取时间段内的每一天

WITH DateSequence AS
 (SELECT CAST('2024-11-01' AS DATE) AS DateValue
 UNION ALL
SELECT DATEADD(DAY, 1, DateValue)FROM DateSequence 
WHERE DATEADD(DAY, 1, DateValue) <= CAST('2024-11-10' AS DATE)
)
SELECT DateValue FROM DateSequence 
OPTION (MAXRECURSION 0);--- 0 代表不限制递归次数。 为防止死循环，sqlserver默认递归100次

--方法二

DECLARE @StartDate DATETIME = '2024/11/01'
DECLARE @EndDate DATETIME ='2024/11/10'

SELECT CONVERT (VARCHAR (100),dateadd(DAY,n.number,@StartDate),23) AS every_time,
CONVERT (VARCHAR (100),dateadd(DAY,n.number,@StartDate),112) AS every_time112
FROM master..spt_values n WHERE type = 'p'AND n.number <= DATEDIFF(day, @StartDate, @EndDate);

 

用法二： 巧用行列转换之 列转行

SELECT Number,
T.col1,t.Col2,t.Col3,t.Col4 -- 为了便于观察与原表区别，此处所有列都要
, RIGHT(LEFT(T.col4,Number-1),
CHARINDEX(',',REVERSE(LEFT(','+T.col4,Number-1))))
FROM
master..spt_values, table1 T
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(T.col4)+1 AND
(SUBSTRING(T.col4,Number,1) = ','
OR SUBSTRING(T.col4,Number,1) = '') --this does not work correctly anyway