SQL 數(shù)據(jù)庫(kù)開(kāi)發(fā)——count開(kāi)窗函數(shù)
開(kāi)窗函數(shù)
count開(kāi)窗函數(shù)
創(chuàng)建表
drop table F0411 ;
create table F0411(
docnum number,
status varchar2(26)
);
插入數(shù)據(jù)
insert into F0411 VALUES (33 , 'FULL') ;
insert into F0411 VALUES (33 , 'NOTFULL') ;
insert into F0411 VALUES (34 , 'FULL') ;
insert into F0411 VALUES (35 , 'FULL') ;
insert into F0411 VALUES (35 , 'NOTFULL') ;
insert into F0411 VALUES (36 , 'FULL') ;
insert into F0411 VALUES (37 , 'FULL') ;
insert into F0411 VALUES (38 , 'FULL') ;
insert into F0411 VALUES (38 , 'NOTFULL') ;
要求：只取狀態(tài)為FULL的DOCNUM，如果有同時(shí)為NOTFULL的DOCNUM則不?。?/p>
select m.* , count(1) over (partition by docnum) as cnt
from F0411 m ;
count(1) over(partition by docnum) count()函數(shù)統(tǒng)計(jì)字段docnum的數(shù)量，再分組字段docnum，不排序僅統(tǒng)計(jì)個(gè)數(shù)。
SELECT t.docnum FROM
(select m.* , count(1) over (partition by docnum) as cnt
from F0411 m) T
WHERE T.STATUS = 'FULL'
AND CNT = 1;
對(duì)T臨時(shí)表進(jìn)行過(guò)濾，讓字段
status等于full，且T表的cnt字段等于1。獲取到唯一值。
鏈接：https://www.dianjilingqu.com/478662.html
