选择每种类型仅具有一个状态值的所有人员：

SELECT PERSON_ID
FROM SGT_PERSON_ACCOUNT
WHERE ACCOUNT_STATUS_VALUE = 'ACTV'
GROUP BY PERSON_ID 
HAVING COUNT(PERSON_ID) = 1  

INTERSECT

SELECT PERSON_ID
FROM SGT_PERSON_ACCOUNT
WHERE ACCOUNT_STATUS_VALUE = 'RFND'
GROUP BY PERSON_ID 
HAVING COUNT(PERSON_ID) = 1

SELECT COUNT(distinct ACCOUNT_STATUS_VALUE), PERSON_ID
FROM SGT_PERSON_ACCOUNT SPAT(NOLOCK)
WHERE ACCOUNT_STATUS_VALUE IN ('ACTV','RFND') --this where clause might be optional according to my understanding
GROUP BY PERSON_ID 
HAVING COUNT(distinct ACCOUNT_STATUS_VALUE) > 1

如果您只需要确定哪些PERSON_ID符合您的标准：

SELECT PERSON_ID
FROM SGT_PERSON_ACCOUNT
GROUP BY PERSON_ID
HAVING COUNT(DISTINCT ACCOUNT_STATUS_VALUE) > 1

如果您需要获取关联信息沿着他们的PERSON_ID：

SELECT * 
FROM SCT_PERSON_ACCOUNT
WHERE PERSON_ID IN (
    SELECT PERSON_ID
    FROM SGT_PERSON_ACCOUNT
    GROUP BY PERSON_ID
    HAVING COUNT(DISTINCT ACCOUNT_STATUS_VALUE) > 1
    )

SELECT 
COUNT(ACCOUNT_STATUS_VALUE) as CT, PERSON_ID
FROM 
SGT_PERSON_ACCOUNT SPAT(NOLOCK)
WHERE
ACCOUNT_STATUS_VALUE IN ('ACTV','RFND')
GROUP BY 
PERSON_ID 
HAVING 
COUNT(DISTINCT ACCOUNT_STATUS_VALUE) > 1

编辑

这里有一个证明，在SELECT中使用COUNT(*)不会改变任何事情。

if object_id('tempdb..#temp') is not null drop table #temp

create table #temp ( PERSON_ID int, ACCOUNT_STATUS_VALUE varchar(20) null, SOMETHING varchar(6))

insert into #temp (PERSON_ID,ACCOUNT_STATUS_VALUE, SOMETHING) values
(1100,'ACTV','A'),
(1100,'ACTV','B'),
(1300,'ACTV','C'),
(1300,'RFND','D')

SELECT 
    COUNT(*), PERSON_ID
    FROM 
    #temp (NOLOCK)
    WHERE
    ACCOUNT_STATUS_VALUE IN ('ACTV','RFND')
    GROUP BY 
    PERSON_ID 
    HAVING 
    COUNT(DISTINCT ACCOUNT_STATUS_VALUE) > 1