同一张表中的SQL SELECT WHERE NOT IN查询

Question

我遇到了以下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

编辑

标签是狗/猫，cpid是临时家庭，用于收养狗/猫。

狗/猫会从一个家庭移动到另一个家庭。

我需要查找在:userinput家庭中的狗/猫，但仅限于它们以前不在其他家庭中的情况。

我不能更改数据库，只能使用现有数据进行操作，而且我不是编写应用程序/数据库模式的人。

- user2002836

---

不用道歉——与其他需要使用拷问手段才能获取信息的问题相比，这是一个写得很好的问题。我会尽力让翻译通俗易懂，但不改变原意。 - Preet Sangha

抱歉，您能解释一下为什么它不应该返回第1行吗？是:cpid = '13%'的原因吗？ - lc.

@Dave，没有错误查询，只返回空。 - user2002836

@Jack 抱歉，编辑了问题标签为狗/猫...现在已经很晚了，感谢你的帮助。 - user2002836

不要在数字上使用 LIKE，这样不高效且可能会得到错误的结果。例如，使用 a.cpid LIKE '135%'，一个值为 1357.16 的数据仍然会通过条件。应该使用：a.cpid >= 135.0 AND a.cpid < 136.0。 - ypercubeᵀᴹ

Answer 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

演示

如果连接条件失败，第二个表别名为 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条件。

- Ja͢ck

Answer 2

我认为这正是你想做的 - 选择每个标签最早的ID，然后从中选择具有135 cpid和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

- Joe

---

“having min(cpdatetime)” 总是为真吗？ - Ja͢ck

你和Jack的解决方案都有效，但我不确定应该标记哪一个为已接受的答案。相比你的回答，我更容易理解Jack的答案@Joe，所以我还不确定，仍在阅读一些更多的输入并深入研究MySQL文档。 - user2002836

嗯，我想是这样的。MySQL只是让我通过group by来欺骗它，并返回最低编号的行，而不是有意义地评估它... 唉。 - Joe

@Joe 这里有一个fiddle，证明仅仅通过交换两行的顺序就会导致错误的结果。 - Ja͢ck