with tmp as ( select 19 sqdid, 1 sftg, 1 dqbz from dual union all select 19 , 0 , 1 from dual union all select 6 , 1 , 1 from dual union all select 6 , 0 , 1 from dual union all select 7 , 0 , 1 from dual union all select 7 , 1 , 1 from dual union all select 8 , 1 , 1 from dual union all select 8 , 0 , 1 from dual union all select 9 , 1 , 1 from dual union all select 9 , 0 , 1 from dual union all select 10 , 1 , 1 from dual union all select 10 , 0 , 1 from dual union all select 18 , 1 , 1 from dual union all select 18 , 0 , 1 from dual union all select 22 , 1 , 1 from dual union all select 22 , 0 , 1 from dual ) SELECT decode(t.sftg,'0','未审批','1','通过','2','拒绝'), COUNT(*) FROM ( SELECT dense_rank() OVER (partition BY a.sqdid ORDER BY a.dqbz DESC, a.sftg DESC) NUM, a.* FROM tmp a ) t WHERE t.num = 1 GROUP BY t.sftg
select 19 sqdid, 1 sftg, 1 dqbz from dual union all
select 19 , 0 , 1 from dual union all
select 6 , 1 , 1 from dual union all
select 6 , 0 , 1 from dual union all
select 7 , 0 , 1 from dual union all
select 7 , 1 , 1 from dual union all
select 8 , 1 , 1 from dual union all
select 8 , 0 , 1 from dual union all
select 9 , 1 , 1 from dual union all
select 9 , 0 , 1 from dual union all
select 10 , 1 , 1 from dual union all
select 10 , 0 , 1 from dual union all
select 18 , 1 , 1 from dual union all
select 18 , 0 , 1 from dual union all
select 22 , 1 , 1 from dual union all
select 22 , 0 , 1 from dual )
SELECT decode(t.sftg,'0','未审批','1','通过','2','拒绝'), COUNT(*) FROM (
SELECT dense_rank() OVER (partition BY a.sqdid ORDER BY a.dqbz DESC, a.sftg DESC) NUM, a.* FROM tmp a ) t
WHERE t.num = 1
GROUP BY t.sftg