cnas

select
"bigTypeName" "标准大类",
regexp_replace(substring("stdCode", '([A-Za-z]*[\s\\/]?[A-Za-z]?\s?\d+(\.\d+)?[\-:：]\d+)'), '[\s/\\]','', 'g') "标准代号" ,

count(distinct "branchId") "机构分支数量"
from cnasability
where "stdCode" is not null
group by "bigTypeName", regexp_replace(substring("stdCode", '([A-Za-z]*[\s\\/]?[A-Za-z]?\s?\d+(\.\d+)?[\-:：]\d+)'), '[\s/\\]','', 'g')
order by "bigTypeName", 2 desc