Как исключить записи с определенными значениями в sql

Как исключить записи с определенными значениями в 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

Возвращает Col1 (и Col2), но только если значение в Col2 равно 1 или 20, но нет, если есть другое значение (кроме 1 или 20)

Желаемый результат:

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

Но не C, D и E, потому что в Col2 есть значение, отличное от 1 или 20

Я использовал фиктивные значения для Col2 и только два значения (1 и 20), но в реальности там еще несколько. Я могу использовать IN ('1', '20') для значений 1 и 20, но как исключить, если есть еще одно значение в Col2. (нет диапазона!)

Ответы

Ответ 1

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

Ответ 2

Используйте 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

Обновление: пока он будет работать для не повторяющегося списка, это может привести к неправильному набору для таблицы с повторяющимися значениями (т.е. 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

(и это тоже будет работать в общем случае)

Ответ 3

Вы можете сделать то же самое с предложением 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