SQL:在两列上进行分区 [英] SQL: partition over two columns

问题描述

我有下表:

---------------------
| No1 | No2  | Amount
---------------------
| A   |  B   |    10 |
| C   |  D   |    20 |
| B   |  A   |    30 |
| D   |  C   |    40 |
---------------------

我想对两列(No1，No2)的分区求和，但是当两列中的值发生变化时，它也应该分组.例如:AB = BA

and I want to sum over partition by both columns (No1,No2) but it should group also when the values are changed in the two columns. Example would be: AB = BA

这将是我的预期结果:

-----------------------------------------
| No1 | No2  | Sum(Amount) over partition
-----------------------------------------
| A   |  B   |    40                    |
| C   |  D   |    60                    |
| B   |  A   |    40                    |
| D   |  C   |    60                    |
-----------------------------------------

有什么想法吗?

推荐答案

使用 least 和 greatest.

select no1,no2,sum(amount) over(partition by least(no1,no2),greatest(no1,no2)) as total
from tbl

这篇关于SQL:在两列上进行分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！