我有这张桌子:
// 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
我的问题:如何实现此条件:
如果
1
是type
然后:如果
0
是type
(这是一个问题),则什么也不选择如果
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
)
)