sql - 基于发生的标志更改

Question

我有下表作为输入

id|type
1|manager
1|non manager
1|manager
1|non manager
1|non manager
1|manager

期望的输出：如果有多个 id 落入 manager 和 non manager hen，它应该显示为混合，如下所示。

id|type
1|mixed

任何人都可以帮助..

标签： sql

Answer 1

一种方法使用窗口函数：

select id,
       (case when max(type) over (partition by id) =
                  min(type) over (partition by id)
             then max(type) over (partition by id)
             else 'Mixed'
        end) as type
from t;

或者，exists我也想到了：

select id,
       (case when exists (select 1
                          from t t2
                          where t2.id = t.id and t2.type <> t.type
                         )
             then 'Mixed' else type
        end) as type
from t;

编辑：

要为每个 id 获取一行，只需使用聚合：

select id,
       (case when max(type) = min(type)
             then max(type)
             else 'Mixed'
        end) as type
from t
group by id;