如何在SQL Server中生成资源可用/已分配/余额动态多行报表？

要生成你需要的包含可用总量、分配明细、剩余余额的报表，我们可以通过UNION ALL将三个不同的数据集合并在一起：资源可用行、项目分配行、剩余余额行。下面是完整的实现方案：

完整SQL查询语句 #

WITH ResourceAllocations AS (
    -- 获取每个资源对应的所有项目分配记录（含未实际分配的项目）
    SELECT 
        A.ResourceName,
        A.iGPMResourceGroupId,
        D.ProjectName,
        'Alloted' AS Status,
        S.Id AS StaffedId,
        S.Staffed01 AS [01],
        S.Staffed02 AS [02],
        A.Id AS ResourceId,
        2 AS SortOrder -- 标记分配行的排序优先级
    FROM AvailableR A
    JOIN DemandR D ON A.iGPMResourceGroupId = D.iGPMResourceGroupId
    LEFT JOIN AllocatedR S ON A.Id = S.AvailableResourceId 
        AND S.ProjectId = D.Number -- 正确关联项目ID与需求编号
        AND S.iGPMResourceGroupId = D.iGPMResourceGroupId
),
ResourceTotals AS (
    -- 计算每个资源的已分配总量（处理NULL值避免求和错误）
    SELECT 
        ResourceName,
        SUM(ISNULL([01], 0)) AS TotalStaffed01,
        SUM(ISNULL([02], 0)) AS TotalStaffed02
    FROM ResourceAllocations
    GROUP BY ResourceName
)
-- 合并三个部分：可用行、分配行、剩余行
SELECT 
    ResourceName,
    iGPMResourceGroupId,
    ProjectName,
    Status,
    StaffedId,
    [01],
    [02]
FROM (
    -- 1. 资源可用总量行
    SELECT 
        ResourceName,
        CAST(NULL AS INT) AS iGPMResourceGroupId,
        CAST(NULL AS VARCHAR(100)) AS ProjectName,
        'Available' AS Status,
        CAST(NULL AS INT) AS StaffedId,
        Capacity01 AS [01],
        Capacity02 AS [02],
        Id AS ResourceId,
        1 AS SortOrder -- 可用行优先显示
    FROM AvailableR

    UNION ALL

    -- 2. 项目分配明细行
    SELECT ResourceName, iGPMResourceGroupId, ProjectName, Status, StaffedId, [01], [02], ResourceId, SortOrder
    FROM ResourceAllocations

    UNION ALL

    -- 3. 剩余余额行（可用-已分配总和）
    SELECT 
        A.ResourceName,
        CAST(NULL AS INT) AS iGPMResourceGroupId,
        CAST(NULL AS VARCHAR(100)) AS ProjectName,
        'Left' AS Status,
        CAST(NULL AS INT) AS StaffedId,
        A.Capacity01 - T.TotalStaffed01 AS [01],
        A.Capacity02 - T.TotalStaffed02 AS [02],
        A.Id AS ResourceId,
        3 AS SortOrder -- 剩余行最后显示
    FROM AvailableR A
    JOIN ResourceTotals T ON A.ResourceName = T.ResourceName
) Combined
ORDER BY ResourceId, SortOrder, ProjectName;

关键逻辑说明 #

1. ResourceAllocations CTE：

   • 关联三张表，完整获取每个资源对应的所有项目需求（包括没有实际分配的项目），同时保留已分配的明细数据。
   • 加入SortOrder字段，确保分配行在可用行之后显示。

2. ResourceTotals CTE：

   • 统计每个资源的已分配总量，用ISNULL处理未分配的NULL值，避免求和结果异常。

3. 三部分数据合并：

   • 可用行：直接从AvailableR提取资源的总容量，对应Status为Available，作为每个资源的首行显示。
   • 分配行：展示每个资源在各个项目的分配情况，未分配的项目会显示NULL值，对应Status为Alloted。
   • 剩余行：通过总容量减去已分配总量计算余额，对应Status为Left，作为每个资源的末行显示。

4. 排序控制：

   • 先按ResourceId分组，确保同一资源的记录聚集在一起；再按SortOrder保证可用→分配→剩余的固定顺序；最后按ProjectName排序分配行，让项目名称有序展示。

修正的关键问题 #

你的现有查询未拆分出可用/剩余的汇总行，且项目分配的关联条件存在偏差（未正确关联ProjectId与Number），导致Staffed01/02显示异常。上述SQL已经修正了这些问题，完全匹配你的预期输出格式。

内容的提问来源于stack exchange，提问作者Vignesh Kumar A