我有下表:

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

我想对两列(No1,No2)进行分区求和,但是当两列中的值更改时,它也应该分组。例如:AB = BA

这将是我的预期结果:
-----------------------------------------
| No1 | No2  | Sum(Amount) over partition
-----------------------------------------
| A   |  B   |    40                    |
| C   |  D   |    60                    |
| B   |  A   |    40                    |
| D   |  C   |    60                    |
-----------------------------------------

有任何想法吗?

最佳答案

使用leastgreatest

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

关于sql - SQL:分为两列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50044809/

10-09 04:23