Oracle數據庫分析函數應用實例之查找狀態全為1的ID是本文我們主要要介紹的內容,通過本文的例子讓我們來一起了解一下Oracle數據庫分析函數的使用吧,希望能夠對您有所幫助。
實例如下:
1、表結構和測試數據插入
建表:
- create table TAB_FXHS
- (
- id VARCHAR2(32),
- zt VARCHAR2(2)
- );
-- Add comments to the table
- comment on table TAB_FXHS
- is '用於統計狀態值全為正常的id值';
-- Add comments to the columns
- 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、 功能需求說明
在表TAB_FXHS中,ID是關鍵字段,ZT是狀態。
ID中可能會有重復的值,現在要求找出ZT全為1的所有ID值。
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;
以上就是Oracle數據庫中分析函數的應用實例之實現查找狀態全為1的ID的全部過程,本文就介紹到這裡了,希望本次的介紹能夠對您有所收獲!