Oracle数据库函数查找状态为1的ID
时间: 2018-09-30 来源: 凯发K

  Oracle数据库分析函数应用实例之查找状态全为1的ID是本文我们主要要介绍的内容,通过本文的例子让我们来一起了解一下Oracle数据库分析函数的使用吧,希望能够对您有所帮助。

  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;

本文源自: 环亚国际



上一篇:尚学堂知识整理:java数组参数
下一篇:管理软件SOA随着普及变得不再神秘