时间戳

首页 > 解决方案 > SQL 检查一条记录是否有来自另一个表的引用，如果有，则执行某些操作

sql - SQL 检查一条记录是否有来自另一个表的引用，如果有，则执行某些操作

问题描述

我有两张桌子，users并且friendships

id   |  name   | password              
-------------------------
 1    |  Dave   | 1234   
-------------------------
 2    |  John   | abcd   
-------------------------
 3    |  Bob    | xyz    
-------------------------

和friendships

 friend_one   |  friend_two   | status              
 -------------------------------------
       1      |      1        |   me
 -------------------------------------
       2      |      2        |   me   
 -------------------------------------
       3      |      3        |   me    
 ------------------------------------
       1      |      2        | pending
 -------------------------------------
       3      |      1        | active

现在，每当用户登录系统时，我需要获取数据库中的所有用户，还需要在 select 查询的结果中添加一个额外的列，显示当前登录用户的友谊状态。

例如，用户 id 1，Dave 登录系统并请求用户列表，选择查询应输出如下，

id   |  name   | password  | friend_status            
------------------------------------------
 1    |  Dave   | 1234     |   me
------------------------------------------ 
 2    |  John   | abcd     |   pending
------------------------------------------
 3    |  Bob    | xyz      |   active
------------------------------------------

当用户 id 2 John 登录系统时，他需要得到下表，

 id   |  name   | password  | friend_status            
------------------------------------------
 1    |  Dave   | 1234     |   pending
------------------------------------------ 
 2    |  John   | abcd     |   me
------------------------------------------
 3    |  Bob    | xyz      |   null
------------------------------------------

这甚至可能吗？我尝试加入表并使用嵌套查询

例如，通过以下查询，我可以获得当前登录用户的所有友谊，

select * from friendships
where friend_one = 2 or friend_two = 2

但我不明白下一步该怎么做。我试图把它作为主选择查询的嵌套查询，并试图加入表，但我没有得到我想要的。

谁能给我一个想法？

标签： sqlpostgresql

解决方案

您需要从表中的值CROSS JOIN列表获取所有不同的值对，然后从表中获取每个用户的朋友：idusersusersidLEFT JOINfriendshipsstatus

SELECT u1.id, u.name, u.password,
       f.status
FROM users u
CROSS JOIN (SELECT DISTINCT id
            FROM users) u1
LEFT JOIN friendships f ON f.friend_one = u.id  AND f.friend_two = u1.id
                        OR f.friend_one = u1.id AND f.friend_two = u.id
ORDER BY u1.id, u.id

输出：

id  name    password    status
1   Dave    1234        me
1   John    abcd        pending
1   Bob     xyz         active
2   Dave    1234        pending
2   John    abcd        me
2   Bob     xyz         (null)
3   Dave    1234        active
3   John    abcd        (null)
3   Bob     xyz         me

SQLFiddle 上的演示

要获取特定用户，只需在子句WHERE u1.id = ?之前添加。ORDER BY

推荐阅读