本文介绍了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
);
这篇关于sql选择具有计数>的记录1 至少有一条记录有价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！