SQL创建年度固定周期完成率表的高效实现方案咨询

Great question! Recursive CTEs get the job done, but for a fixed 12-row dataset like this, they’re a bit overkill—there are lighter, more readable alternatives that skip the recursive overhead entirely. Here are my top recommendations:

1. 直接用VALUES子句构造（最高效最直观） #

Since your month-to-percentage mapping is fixed, hardcoding the values with VALUES is the simplest approach. No recursion, no complex date calculations—just straight, easy-to-audit code:

SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [S/N],
    CONCAT(percent_val, '%') AS Percentage,
    month_name AS Month
FROM (
    VALUES
        (8, 'June'),
        (17, 'July'),
        (25, 'August'),
        (33, 'September'),
        (42, 'October'),
        (50, 'November'),
        (58, 'December'),
        (67, 'January'),
        (75, 'February'),
        (83, 'March'),
        (92, 'April'),
        (100, 'May')
) AS raw_data(percent_val, month_name);

Why this works better: #

• Zero overhead: No recursive CTE calls, so it’s the fastest option for fixed-row datasets.
• Easy to modify: You can tweak percentages or month names directly in the VALUES list without digging through CTE logic.
• Self-documenting: Anyone reading the code immediately sees the exact mapping between months and percentages.

2. Dynamic generation with a number sequence + date functions（更灵活） #

If you want to avoid hardcoding month names (e.g., to adapt to different locales or years), you can generate the month list dynamically using a simple number sequence, then calculate the percentages to match your pattern:

-- This example works for SQL Server; adjust date functions for PostgreSQL/MySQL
WITH month_sequence AS (
    SELECT
        num AS offset,
        DATENAME(MONTH, DATEADD(MONTH, num, DATEFROMPARTS(YEAR(GETDATE()), 6, 1))) AS month_name
    FROM (
        -- Generate 0-11 to cover 12 months starting from June
        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
    ) AS nums(num)
)
SELECT
    ROW_NUMBER() OVER (ORDER BY offset) AS [S/N],
    CONCAT(
        -- Match your percentage pattern: 8 →17 →25 →33 →42 →...→100
        CASE offset
            WHEN 0 THEN 8
            WHEN 1 THEN 17
            WHEN 2 THEN 25
            WHEN 3 THEN 33
            WHEN 4 THEN 42
            WHEN 5 THEN 50
            WHEN 6 THEN 58
            WHEN 7 THEN 67
            WHEN 8 THEN 75
            WHEN 9 THEN 83
            WHEN 10 THEN 92
            WHEN 11 THEN 100
        END,
        '%'
    ) AS Percentage,
    month_name AS Month
FROM month_sequence;

Bonus: If your percentage follows a mathematical pattern #

If those percentages aren’t arbitrary (e.g., alternating +9 and +8 increments), you can replace the CASE statement with a formula to make it even cleaner:

-- Example formula matching your pattern: 8 + (offset // 2)*17 + (offset % 2)*9
CONCAT(8 + FLOOR(offset/2)*17 + (offset % 2)*9, '%') AS Percentage

(Note: Adjust integer division syntax for your SQL dialect—use offset // 2 in PostgreSQL, FLOOR(offset/2) in MySQL)

How this compares to your recursive CTE #

Recursive CTEs are great for variable-length datasets (e.g., generating dates until a future end point), but for a fixed 12 rows, they add unnecessary complexity and execution overhead. Both methods above are faster and easier to maintain than a recursive approach.

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