>我正在尝试获取表中具有 1 条以上记录的所有参与者,其中至少其中一条记录的 IsCurrent = 0 和 IsActive = 1
这是我到目前为止所拥有的,但它不起作用:
SELECT ParticipantId
FROM Contact
WHERE (IsCurrent = 0 AND IsActive = 1 AND ContactTypeId = 1)
Group by ParticipantId
Having COUNT(ParticipantId) > 1
此查询返回与该描述匹配的记录,但我需要与该描述匹配的所有记录,还有更多。
您可以使用 EXISTS:
SELECT ParticipantId
FROM Contact
WHERE EXISTS
( SELECT 1
FROM Contact c2
WHERE c2.ParticipantID = c.ParticipantId
AND ContactTypeId = 1
GROUP BY ParticipantID
HAVING COUNT(*) > 1
AND COUNT(CASE WHEN IsCurrent = 0 AND IsActive = 1 THEN 1 END) >= 1
);
将其用作子查询并连接到它:
select * from
(
SELECT ParticipantId
FROM Contact
WHERE (IsCurrent = 0 AND IsActive = 1 AND ContactTypeId = 1)
Group by ParticipantId
Having COUNT(ParticipantId) > 1
) base
inner join Contact c on c.ParticipantId = base.ParticipantID
WHERE (IsCurrent = 0 AND IsActive = 1 AND ContactTypeId = 1)
select
ParticipantId
from Contact as c
group by
ParticipantId
having
Count(*) > 1
and
Sum(Case when IsCurrent = 0 then 1 else 0 end) >= 1
and
Sum(Case when IsActive = 1 then 1 else 0 end) >= 1
我会先试试这个
我认为您应该删除:
AND ContactTypeId = 1
这似乎是一个 idexed 列
SELECT ParticipantId
FROM Contact
Group by ParticipantId
Having Count(*) > 1
Intersect
SELECT ParticipantId
FROM Contact
WHERE IsCurrent = 0
AND IsActive = 1
AND ContactTypeId = 1