sql - 다음 기준에 대한 선택 쿼리 작성 방법

집합 함수 count() 가 누락되었습니다

select profileid, count(1) as cnt
from CrmProfilePermissions 
join CrmPermissions on CrmProfilePermissions.permissionid=CrmPermissions.permissionid 
where name in ('permission1') and name not in('permission2') 
group by (profileid)

올바른 selectquery를 찾았습니다

입니다

select profileid  from  CrmProfilePermissions
join CrmPermissions on CrmProfilePermissions.permissionid=CrmPermissions.permissionid
where  name in ('permission1','permission2')
group by (profileid)
having count(distinct name)=1;

두 가지 수준의 집계를 권장합니다 :

select has_permission1, has_permission2, count(*),
       min(profileid), max(profileid)  -- I just include these as examples
from (select pp.profileid,
             max(case when p.name = 'permission1' then 1 else 0 end) as has_permission1,
             max(case when p.name = 'permission2' then 1 else 0 end) as has_permission2
      from CrmProfilePermissions pp join
           CrmPermissions p
           on pp.permissionid = p.permissionid
      group by pp.profileid
     ) p
group by has_permission1, has_permission2;