任何人都可以在这里帮助我。我在MySQL数据库中有这样的用户表:
MariaDB [tiro]> SELECT * FROM user_tb;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
| 1 | dias | maria |
| 2 | paulo | heleno |
| 3 | silas | raimundo |
+----+------------+-----------+
这是一个问题:注意:它具有user_tb的外键。
MariaDB [tiro]> SELECT * FROM question_tb;
+----+-------------------------+-------------+
| id | question | asked_by_id |
+----+-------------------------+-------------+
| 1 | whats up | 1 |
| 2 | who is out there | 1 |
| 3 | where do we party today | 3 |
+----+-------------------------+-------------+
和其他答案:注意:它具有for User_tb和Question_tb的外键。
MariaDB [tiro]> SELECT * FROM answer_tb;
+----+---------------------------------+-------------+----------------+
| id | answer | question_id | answered_by_id |
+----+---------------------------------+-------------+----------------+
| 2 | the man in control of you house | 2 | 1 |
| 3 | nothing at all | 1 | 1 |
| 4 | lets party at my house guys | 3 | 1 |
| 5 | casino is the best place | 3 | 2 |
| 6 | its your enemy | 2 | 2 |
| 7 | we are fuck this world | 1 | 3 |
+----+---------------------------------+-------------+----------------+
表具有我需要过滤的所有数据。在这里,我想过滤所有具有ID 1的用户有答案的问题,但排除了其他用户回答该问题的所有答案。
用户ID 1在问题ID 2和用户ID 2上也有一个答案。我怎么能获得一个问题ID 2只有用户1的答案删除用户2。这是可能的吗?我的大脑在这里被锁定,请帮忙。
根据您的问题,您想获取问题2的答案,但只有用户" Dias Maria"给出的答案,其用户ID为1。
几个简单的"在哪里"条款可以实现:
SELECT *
FROM answer_tb
WHERE question_id = 2 AND answered_by_id = 1
如果您希望输出其他详细信息,例如问题文本和用户的名称,也可以加入这些表:
SELECT *
FROM answer_tb
INNER JOIN user_tb ON user_tb.id = answer_tb.answered_by_id
INNER JOIN question_tb ON question_tb.id = answer_tb.question_id
WHERE answer_tb.question_id = 2 AND answer_tb.answered_by_id = 1
这是基本的SQL,所以我建议您做更多的研究,如果这让您头疼。
我不确定我是否正确理解了您的意图,但是如果有的话,我相信答案很简单:
SELECT *
FROM question_tb
JOIN answer_tb ON answer_tb.question_id = question_tb.id
WHERE answered_by_id = 1; <- replace this with your desired ID
此查询仅检索所有现有的问答对并过滤所有对所有对以外的人组成的答案,而不是指定用户的答案。
示例输出:
+----------------+-------------------------+-------------++--------------+---------------------------------+-------------+----------------+
| question_tb.id | question | asked_by_id || answer_tb.id | answer | question_id | answered_by_id |
+----------------+-------------------------+-------------++--------------+---------------------------------+-------------+----------------+
| 1 | whats up | 1 || 3 | nothing at all | 1 | 1 |
| 2 | who is out there | 1 || 2 | the man in control of you house | 2 | 1 |
| 3 | where do we party today | 3 || 4 | lets party at my house guys | 3 | 1 |
+----------------+-------------------------+-------------++--------------+---------------------------------+-------------+----------------+