sql选择具有计数> 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

我试着让表中所有记录超过1的参与者至少有一条记录是IsCurrent = 0 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.

这个查询返回一个与该描述匹配的记录，但是我需要所有与该描述匹配的记录，还有更多。

5 个解决方案

#1

You can use 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
        );

#2

Use it as a subquery and join to it:

使用它作为子查询并连接到它:

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)

#3

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

I would first try this

我先试试这个

#4

I think you should just remove:

我认为你应该删除:

AND ContactTypeId = 1

which seems to be an idexed column

哪个似乎是理想的列

#5

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

#1