我有这张桌子:

// QandA
+----+-----------+-----------------------+-----------+------+---------+
| id |   title   |        content        |  related  | type | deleted |
+----+-----------+-----------------------+-----------+------+---------+
| 1  | question1 | content of question1  | 1         | 0    | 0       |
| 2  | answer1-1 | content of answer1-1  | 1         | 1    | 0       |
| 3  | question2 | content of question2  | 3         | 0    | 0       |
| 4  | answer1-2 | content of answer1-2  | 1         | 1    | 0       |
| 5  | answer2-1 | content of answer2-1  | 3         | 1    | 0       |
+----+-----------+-----------------------+-----------+------+---------+





related列包含其所有答案的问题ID。
type列是0的问题,1列的是答案。
如果不删除问题\ answer,则deleted列为0,而当其删除时,1列为deleted


这也是我的查询:

SELECT * FROM QandA WHERE related = :id AND deleted = 0




我的问题:如何实现此条件:

如果1type然后:


如果0type(这是一个问题),则什么也不选择
如果1是(这是一个答案),则仅不要选择该行

最佳答案

我想您想排除所有已删除问题的答案:

1)删除应为0

2)应该是一个问题,或者(如果是答案)它不应该属于已删除的问题

SELECT * FROM QandA qa
WHERE
    related = :id
    AND deleted = 0
    AND (
        type = 0
        OR NOT EXISTS (
            SELECT * FROM QandA q WHERE q.id = qa.related AND q.deleted
        )
    )

10-07 19:57
查看更多