Question

我正在撰写一份高级的MySQL询问,搜索一个数据库并检索用户信息。如果符合WHERE的条件1,如果满足WHERE的条件2,选择其他领域,我会想到什么?

数据库:用户

------------------------
| user_id | first_name |
------------------------
|    1    |    John    |
------------------------
|    2    |    Chris   |
------------------------
|    3    |     Sam    |
------------------------
|    4    |    Megan   |
------------------------

数据库:友谊

--------------------------------------
| user_id_one | user_id_two | status |
--------------------------------------
|      2      |      4      |    0   |
--------------------------------------
|      4      |      1      |    1   |
--------------------------------------

Status 0 = Unconfirmed
Status 1 = Confirmed

正如你看到约翰和安普;Megan在Chris &被确认为朋友;Megan是朋友,但关系没有得到确认。

The query I am trying to write is as follow: Megan(4) searches for new friends I want all of the users except for the ones she is a confirmed friend with to be returned. So, the results should return 2,3. But since a relationship with user_id 2 exists but is not confirmed, I want to also return the status since an entry in the friendship table does exist between the two. If a user exist but there is no connection in the relationship table it still returns that users information but returns status as a NULL or doesn t return status at all since it doesn t exist in that table.

我希望这一点能够做到。问你是否需要。

Answer 1

为什么不使用左派或没有经验的人?

SELECT users.* 
FROM (users LEFT JOIN friendships 
       ON status=1 AND (user_id_one=user_id OR user_id_two=user_id) )
WHERE 
     status IS NULL

或

SELECT users.* 
FROM users
WHERE 
NOT EXISTS (SELECT * 
            FROM friendships 
            WHERE status=1 
            AND (user_id_one=user_id 
                 OR user_id_two=user_id))

Answer 2

您可以单独提出询问,然后提出<条码>。在每一个问题中,增加一个总价值相同的领域。

因此,这项工作应当做到:

(SELECT id,  Not Friends  As Status FROM t1 WHERE condition1)
UNION
(SELECT id,  Unconfirmed  As Status FROM t1 WHERE condition2)

确实,这两个问题都存在相同的领域数目和名称。

友情链接