WITH DateSequence AS ( SELECT convert(char(10),'2024-01-01',120) AS DateValue UNION ALL SELECT convert(char(10),dateadd(day, 1, DateValue),120) AS DateValue FROM DateSequence WHERE DateValue <'2024-08-13')SELECT DateValue FROM DateSequenceOPTION (MAXRECURSION 0);-- 允许无限递归,或设置一个足够大的数字以避免栈溢出
1.2 跨月数据展开
代码
DROP table if exists #DateRange;-- 创建示例数据表 CREATE TABLE #DateRange ( from_date DATETIME, to_date DATETIME, status VARCHAR(50) -- 假设status是字符型 );-- 插入示例数据 INSERT INTO #DateRange (from_date, to_date, status) VALUES ('2021-05-28 12:00:00.000', '2021-06-21 19:00:00.000', 'Active'), ('2023-12-21 19:00:00.000', '2024-3-22 11:00:00.000', 'NoActive');-- 递归 CTE来展开数据 WITH CTE_DateRange AS ( SELECT from_date, DATEADD(MONTH, DATEDIFF(MONTH, 0, from_date) +1, 0) AS next_month_start, to_date, status FROM #DateRange UNION ALL SELECT next_month_start AS from_date, dateadd(month, 1, next_month_start) AS next_month_start, to_date, status FROM CTE_DateRange WHERE next_month_start < to_date ) SELECT from_date, CASE WHEN next_month_start < to_date THEN next_month_start ELSE to_date END AS to_date, status FROM CTE_DateRange ORDER BY from_date;
1.3 拆分字符串
将字符串拆分为多行
代码
SELECT value AS NumberFROM STRING_SPLIT('123,44,55', ',')
将列值中的字符串拆分为多行
代码
SELECT yt.ID, ss.value AS NumberFROM YourTable ytCROSS APPLY STRING_SPLIT(yt.CommaSeparatedValues, ',') ss
CROSS APPLY
CROSS APPLY 是 SQL Server 中的一个操作符,它用于将表值函数或表值表达式应用到外部查询的每一行,并返回结果集。它类似于 JOIN 操作,但它对每一行都执行右侧表达式,并只返回与外部查询匹配的行。
--simple cross apply exampleDECLARE @JSON NVARCHAR(MAX) = N'[{"OrderNumber":"SO43659","OrderDate":"2011-05-31T00:00:00","AccountNumber":"AW29825","ItemPrice":2024.9940,"ItemQuantity":1},{"OrderNumber":"SO43661","OrderDate":"2011-06-01T00:00:00","AccountNumber":"AW73565","ItemPrice":2024.9940,"ItemQuantity":3}]'SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]FROM OPENJSON ( @JSON ) AS rootCROSS APPLY OPENJSON ( root.value) AS TheValueswhere TheValues.[Key] ='OrderNumber'
2 MySQL
2.1 生成日期列
代码
SET CTE_MAX_RECURSION_DEPTH =3000;WITH RECURSIVE DateSequence AS ( SELECT '2024-01-01' AS DateValue UNION ALL SELECT DATE_ADD(DateValue, INTERVAL 1 DAY) FROM DateSequence WHERE DateValue <'2024-08-13') SELECT DateValue FROM DateSequence;