我有一个简单的任务,老实说,我不知道如何完成。我有来自SQL查询的这些值:
| DocumentNumber | CustomerID |
------------------------------
| AAA | 1 |
| BBB | 1 |
| CCC | 2 |
| DDD | 3 |
-------------------------------
我想显示一些修改后的表,如下所示:
| DocumentNumber | CustomerID | Repeate |
-----------------------------------------
| AAA | 1 | Multiple |
| BBB | 1 | Multiple |
| CCC | 2 | Single |
| DDD | 3 | Single |
------------------------------------------
因此,想法很简单-我需要追加一个新列,并根据客户ID是否存在多次来设置“ Multiple”和“ Single”值
使用窗口功能:
select t.*,
(case when count(*) over (partition by CustomerId) = 1 then 'Single'
else 'Multiple'
end) as repeate
from t;