仅当存在多个条目时才排除项目

Question

我有一个包含策略信息的数据表：

Row  PolicyNumber    Member ID    PolicyName
1    1234            789          Main
2    1265            789          Travel
3    1523            541          Travel
4    6778            374          Main
5    5821            123          Main
6    8763            123          Travel

我想统计每个保单号码中不同MEMBERID的数量。然而，有些会员有不止一个保单号，因为有些会员有旅行政策和主要政策。

因此，我希望SQL首先通过MEMBERID检查POLICYNUMBER的计数，并且如果POLICYNUMBER计数大于1，则只显示POLICYNAME不像"%travel%"的策略编号

但是，如果成员只有一个策略，我希望SQL包含该策略，即使是旅行。

所以在我的样本数据中：

Member 789 would be counted against policy 1234 but not 1265
Member 541 would be counted against policy 541
Member 374 would be counted against policy 6778
Member 123 would be counted against policy 5821 but not 8763

所需的结果将是一个包含两列的表，PolicyNumber和与该策略关联的MemberId的不同计数。

如何编写语法？

Answer 1

您可以使用多个具有条件的来实现这一点

SELECT Member_ID, COUNT(*)
FROM YOUR_TABLE
GROUP BY Member_ID
HAVING COUNT(PolicyName) = 1
OR COUNT(CASE WHEN PolicyName NOT LIKE '%travel%' THEN 1 ELSE 0 END) >= 1;