我有一个员工表,列出了他们的职位编号和一个名为position_sequence的字段,该字段将员工的职位分配为1或2。因此,只有当一个员工有两个职位时,其中一个应分配1,另一行的值应为2。
然而,我想找到一个查询来查找数据的所有问题,其中一名员工只有一个职位,但职位序列是2而不是1。
正确数据(2个位置(:
employee position sequence
------------------------------------
bob builder 1
bob cleaner 2
错误示例(1个位置,但标记为2而不是1(:
employee position sequence
------------------------------------
sally builder 2
您可以使用not exists
:
select t.*
from t
where sequence = 2 and
not exists (select 1 from t t2 where t2.employee = t.employee and t2.sequence = 1);
如果你想要一个将军";是序列缺失";,然后生成序列并显示异常:
select t.*
from (select t.*,
row_number() over (partition by employee order by sequence) as imputed_sequence
from t
) t
where imputed_sequence <> sequence;