时间戳

首页 > 解决方案 > SQL Server 中的按列总计

sql - SQL Server 中的按列总计

问题描述

+---------+-------------+-------------+------------+----------+
|  Item   | Consumption | %Consumption| %Cumulative| Category |
+---------+-------------+-------------+------------+----------+
| Item 1  |         500 |       28.17 |      28.17 | A        |
| Item 2  |         450 |       25.35 |      53.52 | A        |
| Item 3  |         350 |       19.72 |      73.24 | A        |
| Item 4  |         200 |       11.27 |      84.51 | B        |
| Item 5  |         150 |        8.45 |      92.96 | B        |
| Item 6  |         100 |        5.63 |      98.59 | B        |
| Item 7  |          10 |        0.56 |      99.15 | C        |
| Item 8  |           4 |        0.23 |      99.38 | C        |
| Item 9  |           2 |        0.11 |      99.49 | C        |
| Item 10 |           6 |        0.34 |      99.83 | C        |
| Item 11 |           3 |        0.17 |     100.00 | C        |
+---------+-------------+-------------+------------+----------+

我想要ConsumptionPercent显示累积列中的总和。

标签： sqlsql-servertsql

解决方案

以下查询应该做你想做的事：

SELECT [Item]
   ,[Consumption]
   ,[ConsumptionPercent]
   ,SUM([ConsumptionPercent]) OVER (ORDER BY ([Item]) ROWS UNBOUNDED PRECEDING) AS [Cumulative]
   ,[Category]
FROM YourTable

推荐阅读