基于账户范围填充行数据的SQL查询需求

这里有个可行的SQL方案来解决你的问题，核心思路是先提取各层级的标题数据，再通过行号顺序和账户范围关联到Detail行：

步骤1：明确关联规则 #

从你的数据和示例来看：

• Heading_1：对应同一报表下，当前Detail行之前最近的Level_Num=1的Header行标题
• Heading_2：对应同一报表下，账户范围与Detail行完全匹配的Level_Num=11的Total行标题
• Heading_3：对应同一报表下，账户范围与Detail行完全匹配的Level_Num=12的Total行标题

步骤2：SQL实现代码 #

下面是两种可行的写法，你可以根据数据库支持情况选择：

写法一：窗口函数版（推荐，性能更优）

WITH report_with_h1 AS (
    -- 为每行填充最近的Level1标题作为Heading_1
    SELECT 
        *,
        MAX(CASE WHEN Level_Num = 1 THEN Heading_Description END) 
            OVER (PARTITION BY Report_ID ORDER BY Row_Sequence ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Heading_1
    FROM report_template
),
total_heading_2 AS (
    -- 提取Level11的Total标题数据
    SELECT 
        Report_ID,
        Start_Account,
        End_Account,
        Heading_Description AS Heading_2
    FROM report_template
    WHERE Level_Num = 11
),
total_heading_3 AS (
    -- 提取Level12的Total标题数据
    SELECT 
        Report_ID,
        Start_Account,
        End_Account,
        Heading_Description AS Heading_3
    FROM report_template
    WHERE Level_Num = 12
)
-- 关联Detail行与各层级标题
SELECT 
    d.Start_Account,
    d.End_Account,
    d.Heading_1,
    t2.Heading_2,
    t3.Heading_3
FROM report_with_h1 d
LEFT JOIN total_heading_2 t2
    ON d.Report_ID = t2.Report_ID
    AND d.Start_Account = t2.Start_Account
    AND d.End_Account = t2.End_Account
LEFT JOIN total_heading_3 t3
    ON d.Report_ID = t3.Report_ID
    AND d.Start_Account = t3.Start_Account
    AND d.End_Account = t3.End_Account
WHERE d.Row_Type = 'D';

写法二：子查询版（兼容更多数据库）

WITH total_heading_2 AS (
    SELECT 
        Report_ID,
        Start_Account,
        End_Account,
        Heading_Description AS Heading_2
    FROM report_template
    WHERE Level_Num = 11
),
total_heading_3 AS (
    SELECT 
        Report_ID,
        Start_Account,
        End_Account,
        Heading_Description AS Heading_3
    FROM report_template
    WHERE Level_Num = 12
)
SELECT 
    d.Start_Account,
    d.End_Account,
    -- 子查询获取当前Detail行之前最近的Level1标题
    (SELECT TOP 1 Heading_Description
     FROM report_template h
     WHERE h.Report_ID = d.Report_ID
       AND h.Level_Num = 1
       AND h.Row_Sequence < d.Row_Sequence
     ORDER BY h.Row_Sequence DESC) AS Heading_1,
    t2.Heading_2,
    t3.Heading_3
FROM report_template d
LEFT JOIN total_heading_2 t2
    ON d.Report_ID = t2.Report_ID
    AND d.Start_Account = t2.Start_Account
    AND d.End_Account = t2.End_Account
LEFT JOIN total_heading_3 t3
    ON d.Report_ID = t3.Report_ID
    AND d.Start_Account = t3.Start_Account
    AND d.End_Account = t3.End_Account
WHERE d.Row_Type = 'D';

步骤3：关联总账数据 #

得到带标题的Detail行数据后，只需将其与总账表关联，就能为每条总账明细匹配对应的标题：

-- 最终关联总账的查询
WITH report_with_headings AS (
    -- 这里放入上面的CTE和查询部分
    ...
)
SELECT 
    gl.*,
    rh.Heading_1,
    rh.Heading_2,
    rh.Heading_3
FROM general_ledger gl
JOIN report_with_headings rh
    ON gl.Account_Number BETWEEN rh.Start_Account AND rh.End_Account;

验证示例 #

对于Start_Account=3000、End_Account=3099的Detail行，执行查询后会得到：

• Heading_1: CONSTRUCTION INCOME
• Heading_2: TOTAL INCOME
• Heading_3: GROSS PROFIT FROM JOBS
  完全符合你的预期。

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