Exclude rows with a column containing a value if m

问题:

Table has

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

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.

The end resultset should be

User
greg
sally

回答1:

Use not exists:

select distinct
    user
from
    users u
where
    not exists (
       select
           1
       from
           users u2
       where
           u2.user = u.user
           and u2.value = 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).

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

回答2:

    Select distinct User from table
    where User not in ( Select User from table
    where value =284)

回答3:

Another option is a self-join:

SELECT DISTINCT u1.[User]
FROM users u1
LEFT OUTER JOIN users u2 ON u2.[User] = u1.[User] AND u2.Value = 284
WHERE u2.[User] IS NULL

回答4:

You can use group by user and also count how many users with value != 284

Here is SQL

Select   `User`
,count(*)- SUM(CASE WHEN `Value` != 284 THEN 1 ELSE 0 END) 284Val
from table
group by `User`
having 284Val = 0;