SQL Server 在SQL查询中添加合计行(透视表)

jjhzyzn0 于 2023-02-07 发布在其他

关注(0)|答案(2)|浏览(579)

我们正在使用一个资产管理软件，不幸的是无法在DIY实现中得到适当的支持。我希望得到一份资产报告（基于型号和状态的笔记本电脑数量）。
在谷歌的帮助下，我设法为所需的报告创建了一个透视表。现在，我被任务的最后一部分卡住了。这是为了在底部获得资产状态的总计行，例如（店内，新店内）。
这是查询，我不明白如何添加合计行。

SELECT 
  *, 
  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 
FROM 
  (
    SELECT 
      max("product"."COMPONENTNAME") AS "Product", 
      max("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

sql-server

来源：https://stackoverflow.com/questions/75354536/add-total-row-in-sql-query-pivot

2条答案

按热度按时间

5ssjco0h1#

可以使用GROUP BY ... WITH CUBE自动生成两个维的合计。这将添加合计行，并且还将消除显式计算合计列的需要。

SELECT piv.*
FROM (
    SELECT 
      ISNULL(product."COMPONENTNAME", 'Totals:') AS "Product", 
      ISNULL(state."DISPLAYSTATE", 'TOTAL') 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"
      WITH CUBE
) d
pivot (
    sum("Asset Count") for "Asset State" in (
      [In Use], [Used - In Store], [In Store], 
      [New - In Store], [Damaged], [Faulty],
      [TOTAL])
) piv
ORDER BY
    CASE WHEN piv.Product = 'Totals:' THEN 2 ELSE 1 END,
    piv.Product

ISNULL()用于将标签分配给分组的产品和状态名称，否则这些名称将为空。[Total]也已添加到透视列表中，并从最终选择列表中删除。如果要将空值替换为零，则可能需要编辑选择列表以添加ISNULL()函数。（例如ISNULL([In Use], 0) AS [In Use], ...）
结果：
| 产品|使用中|二手-店内|店内|新品-店内|损坏|故障|共计|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| Thinkpad 101笔记本电脑|第二章|第二章|三个|1个|1个|1个|十个|
| Thinkpad 102笔记本电脑|1个|零|零|零|零|零|1个|
| Thinkpad 103笔记本电脑|零|零|零|零|1个|1个|第二章|
| 总计：|三个|第二章|三个|1个|第二章|第二章|十三|
您还可以对行使用GROUP BY ... WITH ROLLUP，并使用"条件聚合"将列定义为透视表的替代，从而编写类似的结果。

SELECT
    ISNULL(product.COMPONENTNAME, 'Total') AS Product,
    COUNT(CASE WHEN state.DISPLAYSTATE = 'In Use' THEN 1 END) AS [In Use],
    COUNT(CASE WHEN state.DISPLAYSTATE = 'Used - In Store' THEN 1 END) AS [Used - In Store],
    COUNT(CASE WHEN state.DISPLAYSTATE = 'In Store' THEN 1 END) AS [In Store],
    COUNT(CASE WHEN state.DISPLAYSTATE = 'New - In Store' THEN 1 END) AS [New - In Store],
    COUNT(CASE WHEN state.DISPLAYSTATE = 'Damaged' THEN 1 END) AS [Damaged],
    COUNT(CASE WHEN state.DISPLAYSTATE = 'Faulty' THEN 1 END) AS [Faulty],
    COUNT(*) AS TOTAL
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 WITH ROLLUP
ORDER BY
    CASE WHEN GROUPING(product.COMPONENTNAME) = 0 THEN 1 ELSE 2 END,
    product.COMPONENTNAME

"条件聚集"是一个非正式术语，用于将SUM()或COUNT()等聚集函数与CASE表达式一起使用。如果条件为true，则THEN子句提供要聚集的数据。如果条件为false，则忽略隐式ELSE null值。
结果：
| 产品|使用中|二手-店内|店内|新品-店内|损坏|故障|共计|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| Thinkpad 101笔记本电脑|第二章|第二章|三个|1个|1个|1个|十个|
| Thinkpad 102笔记本电脑|1个|无|无|无|无|无|1个|
| Thinkpad 103笔记本电脑|无|无|无|无|1个|1个|第二章|
| 共计|三个|第二章|三个|1个|第二章|第二章|十三|
有关使用简化测试数据的示例，请参见this db<>fiddle。

赞(0）回复(0）举报 2023-02-07

b5lpy0ml2#

您可以使用“UNION”将两个查询联接在一起，以给予总计行;它会是这样：

SELECT *, 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
FROM (
SELECT max("product"."COMPONENTNAME") AS "Product", max("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

UNION

SELECT "Totals:", 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
FROM (
SELECT max("product"."COMPONENTNAME") AS "Product", max("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 "state"."DISPLAYSTATE")d

赞(0）回复(0）举报 2023-02-07

我来回答

SQL Server 在SQL查询中添加合计行(透视表)

2条答案

相关问题

热门标签

最新问答