SQL 数据库开发——count开窗函数
开窗函数
count开窗函数
创建表
drop table F0411 ;
create table F0411(
docnum number,
status varchar2(26)
);
插入数据
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') ;
要求：只取状态为FULL的DOCNUM，如果有同时为NOTFULL的DOCNUM则不取；
select m.* , count(1) over (partition by docnum) as cnt
from F0411 m ;
count(1) over(partition by docnum) count()函数统计字段docnum的数量，再分组字段docnum，不排序仅统计个数。
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;
对T临时表进行过滤，让字段
status等于full，且T表的cnt字段等于1。获取到唯一值。
链接：https://www.dianjilingqu.com/478662.html
