问题和答案可能分在不同的表格里；这样你就不会有这个问题了。在这种情况下，您可能会在一对多关系中使用一个简单的连接。你可以用CTE来模拟：

with q as (
    select * from questions_and_answers
    where related is null and deleted = 0
), a as (
    select * from questions_and_answers
    where related is not null and deleted = 0
)
select *
from q left outer join a on a.related = q.related
order by q.id, a.id;

如果答案显示为自己的行是绝对必要的：

with qa as (
    select *, max(deleted) over (partition by coalesce(related, id)) as deleted
    from question_and_answers
)
select * from qa where deleted = 0
order by coalesce(related, id), id;

我只是意识到你使用的是mysql，所以你可能没有可用的cte和/或分析函数。希望这对你还是有用的。

你可以再加一个 AND 运算符使用相关子查询Assert已删除父项的存在，并修改您的条件以获得更优化的查询：

SELECT qa.*
FROM questions_and_answers AS qa
WHERE MATCH(subject,body) AGAINST (:entry)
  AND deleted = 0
  AND (qa.related IS NULL OR
    NOT EXISTS (SELECT 1
      FROM questions_and_answers qaa
      WHERE qa.related = qaa.id AND qaa.deleted = 1)
  )
ORDER BY id DESC
LIMIT :page, 10;

如果只有一级关系——对于任何一行r，存在另一行r'，那么r' .related =r .id ，右。 related IS NULL --你可以把问题和答案连接起来，然后检查问题的答案 deleted 或者使用 NOT EXISTS 检查是否不存在已删除的父记录。 LEFT JOIN 变体：

SELECT qa1.*
       FROM questions_and_answers qa1
            LEFT JOIN questions_and_answers qa2
                      ON qa2.id = qa1.related
       WHERE MATCH(qa1.subject, qa1.body) AGAINST (:entry)
             AND qa1.deleted = 0
             AND coalesce(qa2.deleted, 0) = 0
       ORDER BY qa1.id DESC
       LIMIT :page, 10;
``` `NOT EXISTS` 变体：

SELECT qa1.*
FROM questions_and_answers qa1
WHERE MATCH(qa1.subject, qa1.body) AGAINST (:entry)
AND qa1.deleted = 0
AND NOT EXISTS (SELECT *
FROM questions_and_answers qa2
WHERE qa2.id = qa1.related
AND qa2.deleted = 1)
ORDER BY qa1.id DESC
LIMIT :page, 10;