1、表结构和测试数据插入

　　create table TAB_FXHS

　　(

　　id VARCHAR2(32)，

　　zt VARCHAR2(2)

　　);

　　comment on table TAB_FXHS

　　is '用于统计状态值全为正常的id值';

　　comment on column TAB_FXHS.id

　　is '主键ID';

　　comment on column TAB_FXHS.zt

　　is '状态';

　　prompt Importing table TAB_FXHS…

　　set feedback off

　　set define off

　　insert into TAB_FXHS

　　(ID, ZT)values ('10125', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10161', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10141', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10126', '1');

　　insert into TAB_FXHS (ID, ZT)values ('10102', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10103', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10121', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10121', '1');

　　insert into TAB_FXHS (ID, ZT)values ('10121', '0');

　　insert into TAB_FXHS (ID, ZT)values ('10121', '3');

　　prompt Done.

　　2、 功能需求说明

　　3、功能实现的SQL语句

　　WITH ZT_PARTITION_BY_ID AS

　　(SELECT ID, ZT, COUNT(ZT)

　　OVER(PARTITION BY ID ORDER BY ID)

　　ID_ZT

　　FROM TAB_FXHS

　　GROUP BY ID, ZT)

　　SELECT *

　　FROM ZT_PARTITION_BY_ID

　　WHERE ID_ZT = 1

　　AND ZT = 1;