表T有两条数据 身份证号 单位账号 状态
sfzh dwzh zt
230102196111284834 01 01
230102196111284834 01 02
23010219999999999 01 02
如果整个表里面 有两条或者两条以上的 相同的身份证号 并且 单位账号 相同，同时 状态是01和02的两条数据 就返回身份证号
这个select 怎么写呢？
select sfzh ,dwzh from 表 where zt in ('01','02') group by sfzh ,dwzh having count(distinct zt)=2;
这是要状态01、02同时存在的才显示出来吗？
with dat as
( select '230102196111284834' as sfzh, '01' as dwzh, '01' as zt
from dual
union
select '230102196111284834', '01', '02'
from dual
union
select '23010219999999999', '01', '02'
from dual)
select sfzh
from (select sfzh, dwzh, rank() over(partition by sfzh, dwzh order by zt ) as rn
from dat)
where rn = 2;