我有下表(示例):
+----+--------+-------+------+------------+
| ID | WEIGHT | MONTH | YEAR | CATEGORYID |
+----+--------+-------+------+------------+
| 1 | 0.5 | 1 | 2014 | A |
| 1 | 0.5 | 1 | 2014 | A |
| 1 | 0.5 | 2 | 2014 | A |
| 1 | 0.2 | 2 | 2014 | C |
| 1 | 0.2 | 2 | 2014 | C |
| 2 | 1.0 | 2 | 2014 | B |
| 2 | 1.0 | 2 | 2014 | B |
+----+--------+-------+------+------------+
我想要的输出将是这样的(示例):
+----+--------+-------+------+------------+
| ID | WEIGHT | MONTH | YEAR | CATEGORYID |
+----+--------+-------+------+------------+
| 1 | 1.5 | 1 | 2014 | A |
| 1 | 1.5 | 2 | 2014 | A |
| 1 | 0.4 | 1 | 2014 | C |
| 1 | 0.4 | 2 | 2014 | C |
| 2 | 2.0 | 2 | 2014 | B |
| 2 | 2.0 | 3 | 2014 | B |
+----+--------+-------+------+------------+
因此，当月份休息时，我仍然想将上个月的权重求和到当前的总和，等等。我想对特定ID和CategoryID求和。
最佳答案
希望这行得通。
select DISTINCT ID,sum(WEIGHT) over (partition by categoryid order by categoryid) as WEIGHT,
MONTH,YEAR, CATEGORYID
from table;