sql - 如何添加一列，如另一列的 sum()

Question

我有这张表:

id|Type | Value
--------------
1 |Type1|Value1
2 |Type1|Value2
3 |Type1|Value3
4 |Type2|Value4

我想得到另一列，其值等于相同类型 Type 列的值的 sum() 。像那样:

id|Type | Value | Total
-----------------------
1 |Type1|Value1 |Value1+Value2+Value3
2 |Type1|Value2 |Value1+Value2+Value3
3 |Type1|Value3 |Value1+Value2+Value3
4 |Type2|Value4 |Value4

Answer 1

这是 window functions 的绝佳用例.使用 sum() over (partition by ...):

select id, type, value, sum(value) over (partition by type)
from table1;

Sample SQL Fiddle

示例数据:

| id |  type | value |
|----|-------|-------|
|  1 | Type1 |     1 |
|  2 | Type1 |     2 |
|  3 | Type1 |     3 |
|  4 | Type2 |     4 |

给出示例结果:

| id |  type | value | sum |
|----|-------|-------|-----|
|  1 | Type1 |     1 |   6 |
|  2 | Type1 |     2 |   6 |
|  3 | Type1 |     3 |   6 |
|  4 | Type2 |     4 |   4 |