Блог

У меня есть следующая таблица:

  ---- ------- --------- 
| id | media | status  |
 ---- ------- --------- 
|  1 | FOO   | ACTIVE  |
|  1 | FOO   | PENDING |
| >1 | BAR   | ACTIVE  |
|  2 | FOO   | ACTIVE  |
|  2 | FOO   | PENDING |
| >3 | BAR   | ACTIVE  |
 ---- ------- --------- 
  

Что мне нужно получить, так это список id , в котором есть ACTIVE записи с определенным media значением, но нет PENDING записи с таким же media значением. В моем примере id=1 покрыли FOO , но не раскрыли BAR .

Таким образом, результирующая таблица должна быть:

  ---- 
| id |
 ---- 
|  1 |
|  3 |
 ---- 
  

Единственное решение, которое я вижу, — создать две таблицы с ACTIVE и PENDING записями отдельно, затем найти записи, которые есть только в ACTIVE , а не в PENDING нет. Но я понятия не имею, как построить запрос.

Пожалуйста, сообщите.

Я бы сделал это с помощью group by и having , потому что это очень гибкий способ формулирования запросов такого типа:

 select id
from table t
group by id, media
having sum(status = 'ACTIVE') > 0 and
       sum(status = 'PENDING') = 0;
  

Использование соединения по ЛЕВОМУ краю:-

 SELECT a.id
FROM some_table a
LEFT OUTER JOIN some_table b
ON a.id = b.id
AND a.media = b.media
AND a.status = 'ACTIVE'
AND b.status = 'PENDING'
WHERE b.id IS NULL
  

Этот self присоединяется к таблице, один для активных и один для ожидающих. Проверяет, что b.id ИМЕЕТ значение NULL для проверки отсутствия совпадения.

 SELECT DISTINCT id
  FROM t
 GROUP BY id, media
HAVING MAX(status) = 'ACTIVE';
  

Это предполагает, что идентификатор, который имеет ожидающий носитель, все равно должен быть включен, если он также имеет другой тип носителя без ожидающего. Если это неверно или неприменимо, вы можете удалить DISTINCT из SELECT и media из GROUP BY.

Вы можете использовать предложение NOT EXISTS (также подойдет предложение NOT IN).

 select distinct id
from table t
where status = 'ACTIVE'
and not exists (select null
                  from table
                  where t.id = id and t.media = media
                  and status = 'PENDING')
  

смотрите SQLFiddle

ОБЪЕДИНЕНИЕ ГРУПП

 select id from tbl group by media,id 
having group_concat(status)='active'