sql - 如何使用连接限制查询结果？

Question

我的数据库中有两个表:

类别

id
category
message

消息

id
title
message

我试图用它们的类别检索两条消息。每条消息都可以有多个类别。我试过以下查询:

SELECT categories.category, messages.id, messages.title, messages.message
FROM categories
RIGHT JOIN messages
ON messages.id = category.message
ORDER BY messages.id DESC
LIMIT 2
OFFSET 0

这个的输出是这样的:

category   id   title        message
test-cat   1    Test title   This is the message body
category2  1    Test title   This is the message body

但是，此查询仅产生两行(因为检索到的消息具有多个类别)。如何限制消息数量而不是类别数量？所以结果是这样的:

category   id   title        message
test-cat   1    Test title   This is the message body
category2  1    Test title   This is the message body
test-cat   2    Another msg  This is content
test-cat2  2    Another msg  This is content
something  2    Another msg  This is content

Answer 1

将限制放在子查询中:

SELECT categories.category, m.id, m.title, m.message
FROM categories RIGHT JOIN
     (select *
      from messages
      ORDER BY messages.id DESC
      limit 2
     ) m
     ON m.id = categories.message
ORDER BY m.id DESC