mysql中的Intersect或Minus

Question

我有以下问题，

SELECT DISTINCT (U.uid)
FROM users U
    ,friends F
WHERE U.STATUS = '1'
    AND U.uid = F.friend_two
    AND F.friend_one = '1'
    AND F.ROLE = 'fri'

上面的查询返回32行。

SELECT DISTINCT (U.uid)
FROM users U
    ,friends F
WHERE U.STATUS = '1'
    AND U.uid = F.friend_one
    AND F.friend_two = '1'
    AND F.ROLE = 'fri'

上面的查询返回15行。

我需要组合并取得相交的结果。相交的行是14行(意味着两个表中相同的U.uid是14行)

Answer 1

试试这个：

SELECT *
FROM (
    SELECT DISTINCT (U.uid) UID
    FROM users U
        ,friends F
    WHERE U.STATUS = '1'
        AND U.uid = F.friend_two
        AND F.friend_one = '1'
        AND F.ROLE = 'fri'
    ) A
INNER JOIN (
    SELECT DISTINCT (U.uid) UID
    FROM users U
        ,friends F
    WHERE U.STATUS = '1'
        AND U.uid = F.friend_one
        AND F.friend_two = '1'
        AND F.ROLE = 'fri'
    ) B ON A.UID = B.UID

正如您所指定的，这基本上是两个查询的两个结果集的用户id的交集。

Answer 2

获得交集的最直接方法是简单地连接查询：

SELECT uid FROM (
  SELECT DISTINCT U.uid
  FROM   users U JOIN friends F ON F.friend_two=U.uid AND F.friend_one = '1'
  WHERE  U.status='1' AND F.role='fri'
) NATURAL JOIN (
  SELECT DISTINCT U.uid
  FROM   users U JOIN friends F ON F.friend_one=U.uid AND F.friend_two = '1'
  WHERE  U.status='1' AND F.role='fri'
)

但是，您也可以组合查询并过滤分组的结果：

SELECT   U.uid
FROM     users U JOIN friends F ON (
           F.friend_one = U.uid AND F.friend_two = '1'
         ) OR (
           F.friend_two = U.uid AND F.friend_one = '1'
         )
WHERE    U.status='1' AND F.role='fri'
GROUP BY U.uid
HAVING   SUM(F.friend_one = U.uid AND F.friend_two = '1')
     AND SUM(F.friend_two = U.uid AND F.friend_one = '1')

Answer 3

如果您使用EXISTS而不是joins重写这两个查询，您可以首先删除DISTINCT (这里假设uid是Users的主键)，其次，INTERSECT和EXCEPT (也称为MINUS)操作是清晰的：

查询1:

SELECT U.uid
FROM users U
WHERE U.status = '1'
  AND EXISTS
      ( SELECT *
        FROM friends F
         WHERE U.uid = F.friend_two
           AND F.friend_one = '1'
           AND F.ROLE = 'fri'
      ) ;

查询2:

SELECT U.uid
FROM users U
WHERE U.status = '1'
  AND EXISTS
      ( SELECT *
        FROM friends F
         WHERE U.uid = F.friend_one
           AND F.friend_two = '1'
           AND F.ROLE = 'fri'
      ) ;

查询3: INTERSECT

SELECT U.uid
FROM users U
WHERE U.status = '1'
  AND EXISTS
      ( SELECT *
        FROM friends F
         WHERE U.uid = F.friend_two
           AND F.friend_one = '1'
           AND F.ROLE = 'fri'
      )
  AND EXISTS
      ( SELECT *
        FROM friends F
         WHERE U.uid = F.friend_one
           AND F.friend_two = '1'
           AND F.ROLE = 'fri'
      ) ;

查询4: EXCEPT (**MINUS**)

SELECT U.uid
FROM users U
WHERE U.status = '1'
  AND EXISTS
      ( SELECT *
        FROM friends F
         WHERE U.uid = F.friend_two
           AND F.friend_one = '1'
           AND F.ROLE = 'fri'
      )
  AND NOT EXISTS                       -- notice the NOT here
      ( SELECT *
        FROM friends F
         WHERE U.uid = F.friend_one
           AND F.friend_two = '1'
           AND F.ROLE = 'fri'
      ) ;