代码之家 › 专栏 › 技术社区 › masterhoo

显示sql语句的补码结果

except outer-join left-join sql

4

masterhoo · 技术社区 · 6 年前

我有一个类似于推特的网站,在那里用户可以跟踪其他用户,我需要向他们显示跟踪新用户的建议

TABLE USERS

user id_user
A       1
B       2
C       3
D       4
E       5
F       6

TABLE COMMUNITY

id_follower id_followed
3               4
3               5
3               6

3(C)接至4、5、6(D、E、F)

我得到了显示以下用户的声明,4,5,6(D,E,F):

SELECT user,id_followed
  FROM users,community
    WHERE users.id_user=community.id_followed AND community.id_follower=3
        GROUP BY user

如何显示后面没有C(3)的用户,即1,2(A,B)

我需要一个 EXCEPT ? LEFT OUTER JOIN ?

SELECT id_followed
   FROM community
    EXCEPT
      (
        SELECT user,id_followed
           FROM users,community
                WHERE users.id_user=community.id_followed AND community.id_follower=3
        GROUP BY user
     )

    RETRIEVE ERROR.

3 回复 | 直到 6 年前

1

3

Serkan Arslan 6 年前

您可以使用“不存在”

SELECT *
   FROM users u
WHERE NOT EXISTS
      (
        SELECT *
           FROM community c
                WHERE c.id_follower=3
            AND ( u.id_user = c.id_followed or u.id_user = c.id_follower)
     )

sql fiddle

2

0

Egret 6 年前

简单的方法是使用 minus operator . 它看起来像:

SELECT id_user
 FROM users
MINUS
SELECT id_followed
 FROM community
 WHERE community.id_follower=3

3

0

Khan M 6 年前

你想做的事可以通过使用“不在”子句来实现尝试以下查询以实现您的目标

SELECT * 
FROM 
   users
WHERE 
   id_user not in 
        (SELECT 
            id_followed 
         FROM 
            community 
        WHERE 
            id_follower = 3)
AND 
    id_user <> 3