sql选择具有计数>的记录1 至少有一条记录有价值

问题描述

我试图让所有在表中拥有超过 1 条记录的参与者，其中至少有一条记录的 IsCurrent = 0 和 IsActive = 1

I'm trying to get all participants that have more than 1 record in the table where at lease one of those records has IsCurrent = 0 and IsActive = 1

这是我目前所拥有的，但它不起作用:

This is what I have so far, but it's not working:

    SELECT  ParticipantId 
    FROM Contact
    WHERE (IsCurrent = 0 AND IsActive = 1 AND ContactTypeId = 1)
    Group by ParticipantId
    Having COUNT(ParticipantId) > 1

此查询带回与该描述匹配的记录，但我需要与该描述匹配的所有记录，还有更多.

This query brings back a record that matches that description, but I need all of the records that match that description, there are more.

推荐答案

您可以使用 存在:

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
        );