如何用SQL将无分隔符的Daytype列拆分为多行数据

需求说明 #

将原表中每行的Daytype无分隔符字符串按位置拆分，每个字符对应当月的一天，生成包含Year、Month、Day（两位数字格式）、单个Daytype字符的明细记录。

MySQL 8.0+ 解决方案 #

利用递归CTE生成1到Daytype字符串长度的数字序列，再通过SUBSTRING截取对应位置的字符：

WITH RECURSIVE day_seq AS (
    SELECT 1 AS day_num
    UNION ALL
    SELECT day_num + 1
    FROM day_seq
    WHERE day_num < (SELECT MAX(LENGTH(Daytype)) FROM your_table)
)
SELECT
    TRIM('"' FROM t.Year) AS Year,
    TRIM('"' FROM t.Month) AS Month,
    LPAD(d.day_num, 2, '0') AS Day,
    SUBSTRING(TRIM('"' FROM t.Daytype), d.day_num, 1) AS Daytype
FROM your_table t
JOIN day_seq d ON d.day_num <= LENGTH(TRIM('"' FROM t.Daytype))
ORDER BY Year, Month, Day;

注：TRIM('"' FROM ...)用于去除原字段值中的双引号，LPAD将天数格式化为两位数字。

PostgreSQL 解决方案 #

借助generate_series生成对应天数的序列，结合SUBSTRING拆分字符：

SELECT
    TRIM('"' FROM t.Year) AS Year,
    TRIM('"' FROM t.Month) AS Month,
    LPAD(day_num::TEXT, 2, '0') AS Day,
    SUBSTRING(TRIM('"' FROM t.Daytype), day_num, 1) AS Daytype
FROM your_table t
CROSS JOIN generate_series(1, LENGTH(TRIM('"' FROM t.Daytype))) AS day_num
ORDER BY Year, Month, Day;

注：generate_series可直接生成连续数字，无需递归逻辑。

SQL Server 解决方案 #

通过递归CTE生成数字序列，关联原表完成拆分：

WITH day_seq AS (
    SELECT 1 AS day_num
    UNION ALL
    SELECT day_num + 1
    FROM day_seq
    WHERE day_num < (SELECT MAX(LEN(Daytype)) FROM your_table)
)
SELECT
    REPLACE(t.Year, '"', '') AS Year,
    REPLACE(t.Month, '"', '') AS Month,
    FORMAT(d.day_num, '00') AS Day,
    SUBSTRING(REPLACE(t.Daytype, '"', ''), d.day_num, 1) AS Daytype
FROM your_table t
JOIN day_seq d ON d.day_num <= LEN(REPLACE(t.Daytype, '"', ''))
ORDER BY Year, Month, Day
OPTION (MAXRECURSION 31); -- 设置最大递归数31，覆盖所有月份最多天数

注：REPLACE用于去除双引号，FORMAT生成两位格式的天数，MAXRECURSION限制递归次数避免性能问题。

内容的提问来源于stack exchange，提问作者Satya