如果存在多行，则排除列包含值的行 [英] Exclude rows with a column containing a value if multiple rows exist for

问题描述

表有

User   Value
john   284
john   200
john   5
sally  245
sally  180
sally  10
bill   90
bill   1000
bill   284
greg   10
greg   90
greg   2000

例如，如果 User 的值为 284，那么我希望结果集不包括他我不知道如何检查 User 的所有行以查看是否存在 284 值，然后在结果集中不显示该用户(如果存在).结果集应该是不同的.

If User has a value of 284 for example, then I want the result set not to include him I am not sure how to check all rows of User to see if there is the 284 value and then not show that user in the resultset if it is there. The resultset should be distinct.

最终结果集应该是

User
greg
sally

推荐答案

使用不存在:

select distinct
    user
from
    users u
where
    not exists (
       select
           1
       from
           users u2
       where
           u2.user = u.user
           and u2.value = 284
    )

它的作用是从 users 表中获取所有用户，其中在 users284 的行代码>表.您也可以将 exists 作为反向操作(仅查找具有 284 值的用户).

What this does is it grabs all the users from the users table where they don't have a row with the value 284 in the users table. You can also do exists as a converse (finding only users with a 284 value).

此外，在 select 上使用 distinct 来限制返回其唯一值的用户.

Additionally, use a distinct on the select to limit the users returned to their unique values.

这篇关于如果存在多行，则排除列包含值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！