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
标签:..,col4,spt,Number,values,master From: https://www.cnblogs.com/Vliana/p/18531864