如何选择只有当内连接的最后一个元素匹配?

Question

我有两个表:processes和notes。每个音符都链接到一个过程。一个流程有几个注释(一对多关系)。Notes也有创建日期。

我想选择每个进程的最后一个笔记包含一个特定的文本(说'一些内容')，但只有当这个笔记是最后一个创建的进程。

例如:

流程表:

id | name
----------
42 | 'foo'

表:

content       | creation_date | process_id
-------------------------------------------
'note1'       | '09/13'       | 42
'note1'       | '09/14'       | 42
'some_content'| '09/15'       | 42

notes中的process_id字段为外键。在这个例子中，'foo'进程应该由我的查询选择。

如果添加了新的注释，notes表变成如下所示:

content       | creation_date | process_id
-------------------------------------------
'note1'       | '09/13'       | 42
'note1'       | '09/14'       | 42
'some_content'| '09/15'       | 42
'note4'       | '09/16'       | 42

在这种情况下，'foo'进程不应该被选中，因为最后一个注释内容不再是'some_content'了。

有可能在单个查询中做这样的事情吗?

我正在使用MySQL

Answer 1

一种可能是聚合:

select p.id, p.name
from processes p join
notes n
on n.process_id = p.id
group by p.id, p.name
having max(n.creation_date) = max(case when n.note like '%some_content%' then n.creation_date end);

Answer 2

您可以使用这样的关联子查询:

SELECT *
FROM processes
WHERE (
SELECT content
FROM notes
WHERE notes.process_id = processes.id
ORDER BY creation_date DESC
LIMIT 1
) = 'some_content'

Answer 3

还有一个方法就是使用exists

select * 
from processes p
where exists (
select * from notes n
where n.process_id=p.id 
and n.content='some_content' 
and n.creation_date=(select Max(creation_date) from notes)
)