MySQL 如何在sql中排除具有某些值的记录

How to exclude records with certain values in sql

提问by John Doe

How to exclude records with certain values in sql (MySQL)

如何在 sql (MySQL) 中排除具有特定值的记录

Col1    Col2
-----   -----
A       1
A       20
B       1
C       20
C       1
C       88
D       1
D       20
D       3
D       1000
E       19
E       1

Return Col1 (and Col2), but only if the value in Col2 is 1 or 20, but not if there's also another value (other than 1 or 20)

返回 Col1（和 Col2），但仅当 Col2 中的值是 1 或 20 时才返回，但如果还有另一个值（1 或 20 除外），则返回

Desired result:

想要的结果：

Col1    Col2
-----   -----
A       1
A       20
B       1

But not C,D and E because there's a value in Col2 other than 1 or 20

I've used fictitious values for Col2 and only two values (1 and 20) but in real there some more.
I can use IN ('1', '20') for the values 1 and 20 but how to exclude if there's also another value in Col2. (there's no range !)

但不是 C、D 和 E，因为 Col2 中有一个值而不是 1 或 20

我使用了 Col2 的虚构值并且只有两个值（1 和 20），但实际上还有更多。
我可以将 IN ('1', '20') 用于值 1 和 20 但如何排除 Col2 中是否还有另一个值。（没有范围！）

回答by Mudassir Hasan

Select col1,col2
From table
Where col1 not in (Select col1 from table where col2 not in (1,20))

回答by Alma Do

Use SUM()

用 SUM()

SELECT
  *
FROM
  t
  INNER JOIN
  (SELECT
     SUM(IF(Col2 IN (1, 20), 1, -1)) AS ranges,
     col1
  FROM
    t
  GROUP BY
     col1
  HAVING
    ranges=2) as counts 
 ON counts.col1=t.col1

Update: while it will work for non-repeated list, it may result in wrong set for table with repeated values (i.e. 1, 20, 20, 1in column - it will still fit request if repeats are allowed, but you've not mentioned that). For case with repeats where's a way too:

更新：虽然它适用于非重复列表，但它可能会导致表的重复值设置错误（即1，列中的, 20, 20,1如果允许重复，它仍然适合请求，但您没有提到）。对于重复的情况，也是一种方法：

SELECT 
  t.* 
FROM 
  t 
  INNER JOIN 
    (SELECT 
       col1, 
       col2 
     FROM 
       t 
     GROUP BY 
       col1 
     HAVING 
       COUNT(DISTINCT col2)=2 
       AND 
       col2 IN (1, 20)) AS counts 
    ON test.col1=counts.col1

(and that will work in common case too, of course)

（当然，这也适用于普通情况）

回答by Mari

You can do the same with NOT EXISTSclause also,

你也可以对NOT EXISTS子句做同样的事情，

Select A.COL1,A.COL2
From MYTABLE A
where NOT EXISTS
(Select COL1 from MYTABLE B where  A.COL1=B.COL1 and
 COL2 NOT IN (1,20)
GROUP BY COL1)

liveDemo

现场演示