Beweeted的社区主页

我写这篇文章有两种方式。两者都使用到g_users表的额外连接。第一个是一个查询,它可以得到您想要的输出,而第二个(在我看来)更可读,但使用了一个并集,从技术上讲是两个查询。

它们的输出相同。

沙盒设置:

CREATE TABLE g_relationships (
    id INT PRIMARY KEY,
    request_id INT,
    receiver_id INT,
    status VARCHAR(10),
    approved VARCHAR(10)
);

CREATE TABLE g_users (
    id INT PRIMARY KEY,
    username VARCHAR(20)
);

INSERT INTO g_users (id, username) VALUES
(1, "Jane"),
(2, "Bob"),
(3, "Sally"),
(4, "Frank");

INSERT INTO g_relationships (id, request_id, receiver_id, status, approved) VALUES
(1, 1, 3, "Friend", "No"),
(2, 1, 4, "Enemy", "No"),
(3, 4, 3, "Friend", "Yes"),
(4, 4, 2, "Friend", "Yes");

超级加入:

SELECT u.id, rel.request_id, rel.receiver_id, rel.status, rel.approved, u2.username AS FriendName
FROM g_relationships AS rel
JOIN g_users AS u
  ON (rel.request_id = u.id OR rel.receiver_id = u.id)
JOIN g_users AS u2
  ON (rel.request_id = u2.id OR rel.receiver_id = u2.id)
WHERE u.id != u2.id
  AND u.id = 4;

工会:

SELECT u.id, rel.request_id, rel.receiver_id, rel.status, rel.approved, u2.username AS FriendName
FROM g_relationships AS rel
JOIN g_users AS u
  ON rel.request_id = u.id
JOIN g_users AS u2
  ON rel.receiver_id = u2.id
WHERE u.id = 4

UNION

SELECT u.id, rel.request_id, rel.receiver_id, rel.status, rel.approved, u2.username AS FriendName
FROM g_relationships AS rel
JOIN g_users AS u
  ON rel.receiver_id = u.id
JOIN g_users AS u2
  ON rel.request_id = u2.id
WHERE u.id = 4;