如果 1 行满足条件,则排除 ID 的所有行

exclude all rows for an ID if 1 row meets condition(如果 1 行满足条件,则排除 ID 的所有行)
本文介绍了如果 1 行满足条件,则排除 ID 的所有行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

如果某些客户没有列出监护人,我正在尝试从他们的联系人表中选择他们.

I am trying to select certain customers from a contacts table if they do not have a guardian listed.

ClientId | ContactId | Guardian
123      | 1         | Y
123      | 2         | N
123      | 3         | N

456      | 4         | N
456      | 5         | N
456      | 6         | N

所需的输出:

ClientId | ContactId | Guardian 
456      | 4         | N
456      | 5         | N
456      | 6         | N

所以我的目标是客户端 456 会出现在我的查询结果中,但 不会 客户端 123.我写了以下内容:

So my goal is that Client 456 would show up in my query results, but not Client 123. I have written the following:

select * from Contacts
where Guardian <> (case when Guardian = 'Y' 
    then Guardian 
       else '' 
         end)

我也试过

select * from Contacts c
where not exists (select 1
                  from Contacts c2
                  where c2.ContactId = c.ContactId
                  and c.Guardian = 'Y')                      

但我的结果只是排除了 Guardian = Y 的行,并打印了 Guardian = N 的行,即使有任何行与 Guardian = Y 的 ClientId 相关联,该 ClientId 也不应该出现在结果中.我一直在寻找如何只选择其中具有某些值的行,但是如果其中一行匹配,我没有找到如何完全排除 ClientId 的运气.

But my results are just excluding the lines where Guardian = Y, and printing the lines where Guardian = N, even though if there is any line associated with a ClientId where Guardian = Y, that ClientId should not show up in the results. I've been looking up how to only select rows with certain values in them, but I'm not having any luck finding how to exclude a ClientId entirely if one of its rows matches.

如果有任何建议,我将不胜感激!

I'd be really grateful for any suggestions!

推荐答案

子查询获取没有任何 Guardian = 'Y'ClientId.如果您需要完整的记录,也可以使用外部查询.如果您只需要 ID,则只需使用子查询

The subquery gets the ClientIds that do not have any Guardian = 'Y'. If you need the complete record use the outer query too. If you need only the ID then use just the subquery

select *
from Contacts
where ClientId in
(
   select ClientId
   from Contacts
   group by ClientId
   having sum(case when Guardian = 'Y' then 1 else 0 end) = 0
)                

这篇关于如果 1 行满足条件,则排除 ID 的所有行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Error casting varchar containing a number expressed in scientific notation to decimal(将包含以科学记数法表示的数字的 varchar 转换为十进制时出错)
Calculate cumulative product value(计算累积产品价值)
SQL Server loop for changing multiple values of different users(用于更改不同用户的多个值的 SQL Server 循环)
SQL Server 2008: Can a multi-statement UDF return a UDT?(SQL Server 2008:多语句 UDF 能否返回 UDT?)
Merge columns in sql(合并sql中的列)
SQL Column is invalid in ORDER BY, not contained in aggregate or GROUP BY(SQL 列在 ORDER BY 中无效,未包含在聚合或 GROUP BY 中)