SQL SELECT WHERE NOT IN из одного запроса таблицы

У меня возникла проблема со следующим SQL-запросом и MySQL

SELECT
  id, cpid, label, cpdatetime
FROM
  mytable AS a
WHERE
  id NOT IN
  (
    SELECT
      id
    FROM
      mytable AS b
    WHERE
      a.label = b.label
    AND
      a.cpdatetime > b.cpdatetime
  )
AND
  label LIKE 'CB%'
AND
  cpid LIKE :cpid
GROUP BY label
ORDER BY cpdatetime ASC

таблица выглядит так:

1 | 170.1 | CB55 | 2013-01-01 00:00:01
2 | 135.5 | CB55 | 2013-01-01 00:00:02
3 | 135.6 | CB59 | 2013-01-01 00:00:03
4 | 135.5 | CM43 | 2013-01-01 00:00:04
5 | 135.5 | CB46 | 2013-01-01 00:00:05
6 | 135.7 | CB46 | 2013-01-01 00:00:06
7 | 170.2 | CB46 | 2013-01-01 00:00:07

Я хочу, чтобы мой запрос возвращался

3 | 135.6 | CB59
5 | 135.5 | CB46

Edit

метки - собаки/кошки, а cpids - временная семья, содержащая собак/кошек.

Собаки/кошки перемещаются из семьи в семью.

Мне нужно найти собак/кошек, которые были в: userinput family, но только если они не были в другой семье ранее

Я не могу изменить базу данных и просто работать с данными так, как они есть, и я не тот, кто написал схему приложения/базы данных.

Ответ 1

Попробуйте избежать коррелированных подзапросов с помощью LEFT JOIN:

SELECT a.id, a.cpid, a.label, a.cpdatetime
FROM mytable AS a
LEFT JOIN mytable AS b ON a.label = b.label AND a.cpdatetime > b.cpdatetime
WHERE a.label LIKE 'CB%' AND a.cpid LIKE :cpid
  AND b.label IS NULL
GROUP BY a.label
ORDER BY a.cpdatetime ASC

Fiddle

Если условие объединения не выполняется, поля второго псевдонима таблицы b будут установлены на NULL.

В качестве альтернативы используйте некоррелированный подзапрос:

SELECT a.id, a.cpid, a.label, a.cpdatetime
FROM mytable AS a
INNER JOIN (
  SELECT label, MIN(cpdatetime) AS cpdatetime
  FROM mytable
  WHERE label LIKE 'CB%'
  GROUP BY label
) AS b ON a.label = b.label AND a.cpdatetime = b.cpdatetime
WHERE a.cpid LIKE '135%'
ORDER BY a.cpdatetime

Сначала вы найдете минимальный cpdatetime для каждой метки, а затем присоедините его к первой таблице, где вы добавите дополнительное условие cpid.

Ответ 2

Я думаю, что это действительно то, что вы хотите сделать - выберите идентификаторы, которые являются самыми ранними идентификаторами для каждой метки, а затем выберите из них записи с 135 кпд и меткой CB.

SELECT
  A.id, cpid, A.label, cpdatetime
FROM
  mytable AS a inner join
 (select id, label from mytable
  group by label
  having min(cpdatetime)) as b
on A.label=B.label and A.id=B.id
WHERE
  A.label LIKE 'CB%'
AND
  cpid LIKE '135%'
GROUP BY A.label
ORDER BY cpdatetime ASC;

http://sqlfiddle.com/#!2/ccccf/16