MySQL删除子查询中的重复子句

Question

我正在尝试简化我的查询，以便它只包含一次 session ID (SID)。

Users表的抽象结构是:

+----+------+----------+
| ID | Name | Username |
+----+------+----------+

Friends 表的抽象结构如下:

+----+-----------------+----------+--------+---------+
| ID |     UserID      | FriendID | Hidden | Deleted |
|    | (Foreign key    |          |        |         |
|    | of ID in Users) |          |        |         |
+----+-----------------+----------+--------+---------+

Session表的抽象结构:

+----+-----------------+-----+
| ID |     UserID      | SID |
|    | (Foreign key    |     |
|    | of ID in Users) |     |
+----+-----------------+-----+

我有以下查询，它改编 self 之前的一个问题的答案。如您所见， session ID (SID) 重复了 4 次，是否可以将查询压缩为一个整体，以便只需要一次 SID？

SELECT *
    ,CASE 
        WHEN D.ID IS NULL
            THEN "Wants to be your friend"
        ELSE "Friends"
        END AS STATUS
FROM (
    SELECT DISTINCT A.ID
        ,A.NAME
        ,E.Hidden
    FROM Users A
    INNER JOIN Friends E ON A.ID = E.UserID
    WHERE A.ID IN (
            SELECT A.UserID
            FROM Friends A
            INNER JOIN Sessions S ON A.FriendID = S.UserID
            WHERE S.SID = "1234"
                AND Deleted = 'No'
            )
    ) C
LEFT JOIN (
    SELECT DISTINCT B.ID
        ,B.NAME
        ,F.Hidden
    FROM Users B
    INNER JOIN Friends F ON B.ID = F.FriendID
    WHERE B.ID IN (
            SELECT A.FriendID
            FROM Friends A
            INNER JOIN Sessions S ON A.UserID = S.UserID
            WHERE S.SID = "1234"
                AND Deleted = 'No'
            )
    ) D ON C.ID = D.ID

UNION

    DISTINCT
SELECT *
    ,CASE 
        WHEN C.ID IS NULL
            THEN "Request Sent"
        ELSE "Friends"
        END AS STATUS
FROM (
    SELECT DISTINCT A.ID
        ,A.NAME
        ,E.Hidden
    FROM Users A
    INNER JOIN Friends E ON A.ID = E.UserID
    WHERE A.ID IN (
            SELECT A.UserID
            FROM Friends A
            INNER JOIN Sessions S ON A.FriendID = S.UserID
            WHERE S.SID = "1234"
                AND Deleted = 'No'
            )
    ) C
RIGHT JOIN (
    SELECT DISTINCT B.ID
        ,B.NAME
        ,F.Hidden
    FROM Users B
    INNER JOIN Friends F ON B.ID = F.FriendID
    WHERE B.ID IN (
            SELECT A.FriendID
            FROM Friends A
            INNER JOIN Sessions S ON A.UserID = S.UserID
            WHERE S.SID = "1234"
                AND Deleted = 'No'
            )
    ) D ON C.ID = D.ID

解释系统的一个基本方法是，如果两个用户是 friend ，那么数据库中就有两条记录。一个从第一个用户到第二个用户，另一个记录从第二个用户到第一个用户。

如果当前用户对另一个用户有记录则发送好友请求，如果一个用户对当前用户有记录则接收好友请求。

这是它如何工作的范图:

SQL fiddle -http://sqlfiddle.com/#!2/c5587/1

Answer 1

SQL fiddle :http://sqlfiddle.com/#!2/06e08/68/0

此返回 Friends and Request Sent :

SELECT 
  f.FriendID,
  u.Name,
  f.Hidden,
  CASE
    WHEN reqs.FriendID IS NULL
      THEN "Request Sent"
    WHEN reqs.FriendID = f.UserID
      THEN "Friends"
  END AS Status
FROM
  Friends AS f
  INNER JOIN
    Sessions AS s
    ON f.UserId = s.UserID
  INNER JOIN
    Users AS u
    ON u.ID = f.FriendID
  LEFT JOIN
    Friends AS reqs
    ON reqs.FriendID = f.UserID 
      AND reqs.UserID = f.FriendID
WHERE
  s.SID = "sid1"

如果您还想要 Request Received，请附加:

UNION

SELECT
  f.UserID,
  u.Name,
  f.Hidden,
  "Request Received" AS Status
FROM
  Friends AS f
  INNER JOIN
    Sessions AS s
    ON f.FriendID = s.UserID
  INNER JOIN
    Users AS u
    ON u.ID = f.UserID
WHERE 
  f.UserID NOT IN 
    (
      SELECT 
        ff.FriendID 
      FROM 
        Friends AS ff 
        INNER JOIN
          Sessions AS ss
          ON ff.UserID = ss.UserID
      WHERE ss.SID = "sid1"
    )
  AND s.SID = "sid1"

不知道如何优化最后一部分。由于它是 SELF JOIN，所以这该死的思维扭曲者。

我知道这不是您所期望的，但是，我无法获取所有 SID，但是这个请求应该比您当前使用的请求更快