sql-server - Sum、Group by 和 Null

Question

我正在深入研究 SQL。我有下表

+------+----+------+------+-------+
| Type | ID | QTY  | Rate | Name  |
+------+----+------+------+-------+
| B    |  1 | 1000 |   21 | Jack  |
| B    |  2 | 2000 |   12 | Kevin |
| B    |  1 | 3000 |   24 | Jack  |
| B    |  1 | 1000 |   23 | Jack  |
| B    |  3 |  200 |   13 | Mary  |
| B    |  2 | 3000 |   12 | Kevin |
| B    |  4 | 4000 |   44 | Chris |
| B    |  4 | 5000 |   43 | Chris |
| B    |  3 | 1000 |   26 | Mary  |
+------+----+------+------+-------+

我不知道如何利用 Sum 和 Group by 来实现以下结果。

+------+----+------+------+-------+------------+
| Type | ID | QTY  | Rate | Name  | Sum of QTY |
+------+----+------+------+-------+------------+
| B    |  1 | 1000 |   21 | Jack  | 5000       |
| B    |  1 | 3000 |   24 | Jack  | Null       |
| B    |  1 | 1000 |   23 | Jack  | Null       |
| B    |  2 | 3000 |   12 | Kevin | 5000       |
| B    |  2 | 3000 |   12 | Kevin | Null       |
| B    |  3 |  200 |   13 | Mary  | 1200       |
| B    |  3 | 1000 |   26 | Mary  | Null       |
| B    |  4 | 4000 |   44 | Chris | 9000       |
| B    |  4 | 5000 |   43 | Chris | Null       |
+------+----+------+------+-------+------------+

任何帮助表示赞赏！

标签： sql-server

Answer 1

您可以使用窗口功能：

select t.*,
       (case when row_number() over (partition by type, id order by name) = 1
             then sum(qty) over (partition by type, id order by name) 
        end) as Sum_of_QTY
from table t;