在SQL透视（Pivot）查询中添加总计行的技术咨询

可以通过UNION ALL将原透视结果与总计行的查询结果合并，实现报表底部显示各状态的总计值。以下是修改后的完整SQL：

-- 原透视查询：展示各型号各状态的资产数量
SELECT 
  Product,
  ISNULL([In Use], 0) AS [In Use],
  ISNULL([Used - In Store], 0) AS [Used - In Store],
  ISNULL([In Store], 0) AS [In Store],
  ISNULL([New - In Store], 0) AS [New - In Store],
  ISNULL([Damaged], 0) AS [Damaged],
  ISNULL([Faulty], 0) AS [Faulty],
  ISNULL([In Use], 0) + ISNULL([Used - In Store], 0) + ISNULL([In Store], 0) + ISNULL([New - In Store], 0) + ISNULL([Damaged], 0) + ISNULL([Faulty], 0) AS TOTAL,
  1 AS SortOrder -- 用于排序，确保原数据排在前面
FROM 
  (
    SELECT 
      "product"."COMPONENTNAME" AS "Product", 
      "state"."DISPLAYSTATE" AS "Asset State", 
      count("resource"."RESOURCENAME") AS "Asset Count" 
    FROM 
      "Resources" "resource" 
      LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID" = "product"."COMPONENTID" 
      LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID" = "state"."RESOURCESTATEID" 
      LEFT JOIN "ResourceOwner" "rOwner" ON "resource"."RESOURCEID" = "rOwner"."RESOURCEID" 
      LEFT JOIN "ResourceAssociation" "rToAsset" ON "rOwner"."RESOURCEOWNERID" = "rToAsset"."RESOURCEOWNERID" 
      LEFT JOIN "SDUser" "sdUser" ON "rOwner"."USERID" = "sdUser"."USERID" 
      LEFT JOIN "AaaUser" "aaaUser" ON "sdUser"."USERID" = "aaaUser"."USER_ID" 
    WHERE 
      product.COMPONENTNAME LIKE ('Thinkpad%') 
    GROUP BY 
      "product"."COMPONENTNAME", 
      "state"."DISPLAYSTATE"
  ) d pivot (
    sum("Asset Count") for "Asset State" in (
      [In Use], [Used - In Store], [In Store], 
      [New - In Store], [Damaged], [Faulty]
    )
  ) piv

UNION ALL

-- 总计行查询：计算所有型号各状态的总和
SELECT 
  'Total' AS Product,
  SUM(CASE WHEN "state"."DISPLAYSTATE" = 'In Use' THEN 1 ELSE 0 END) AS [In Use],
  SUM(CASE WHEN "state"."DISPLAYSTATE" = 'Used - In Store' THEN 1 ELSE 0 END) AS [Used - In Store],
  SUM(CASE WHEN "state"."DISPLAYSTATE" = 'In Store' THEN 1 ELSE 0 END) AS [In Store],
  SUM(CASE WHEN "state"."DISPLAYSTATE" = 'New - In Store' THEN 1 ELSE 0 END) AS [New - In Store],
  SUM(CASE WHEN "state"."DISPLAYSTATE" = 'Damaged' THEN 1 ELSE 0 END) AS [Damaged],
  SUM(CASE WHEN "state"."DISPLAYSTATE" = 'Faulty' THEN 1 ELSE 0 END) AS [Faulty],
  COUNT("resource"."RESOURCENAME") AS TOTAL,
  2 AS SortOrder -- 用于排序，确保总计行排在最后
FROM 
  "Resources" "resource" 
  LEFT JOIN "ComponentDefinition" "product" ON "resource"."COMPONENTID" = "product"."COMPONENTID" 
  LEFT JOIN "ResourceState" "state" ON "resource"."RESOURCESTATEID" = "state"."RESOURCESTATEID" 
WHERE 
  product.COMPONENTNAME LIKE ('Thinkpad%')

ORDER BY SortOrder, Product;

关键说明： #

• UNION ALL合并结果：将原型号明细数据和总计行数据合并，注意两边的列数、列名和数据类型必须完全匹配
• SortOrder排序字段：新增SortOrder列，让原数据以1标记，总计行以2标记，最后通过ORDER BY确保总计行显示在报表底部
• 总计行计算：直接从原始表中统计各状态的总数，避免重复计算，保证结果准确
• 空值处理：原查询中用ISNULL将空值转为0，确保合计计算正确

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