mysql - 艰难的mysql查询，需要用户之间共同的词

Question

我有以下 mysql 查询:

SELECT u.name_surname, 
       u.avatar,
       GROUP_CONCAT(DISTINCT w.word ORDER BY w.word ASC) AS asker_words,
       (a.friend_id) AS asker_id
FROM users u
INNER JOIN 
(
SELECT f1.asker_user_id AS friend_id
    FROM friends AS f1 
    LEFT JOIN friends AS f2
        ON f1.asked_user_id = f2.asker_user_id
        AND f1.asker_user_id = f2.asked_user_id   
    WHERE f1.status = 1 AND f2.status IS NULL
    AND f1.asked_user_id = :user_id
) a ON a.friend_id = u.id
LEFT JOIN connections c ON u.id = c.user_id
LEFT JOIN words_en w ON w.id = c.word_id
        WHERE c.invisible <> 1
          AND c.deleted <> 1
GROUP BY 1

它的作用本质上是向我提供针对当前用户 (asked_user_id) 的好友请求。

Friends table is simple:
id | asker_user_id | asked_user_id | status | created

上面的查询向我提供了向当前用户发起好友请求的用户，以及提出请求的用户在其帐户下的字词。

我遇到两个问题，无法解决。

1) 此查询获取提问者用户的所有单词。相反，我想抓取“询问”和“询问”有共同点/相同减号设置为不可见或已删除的单词。

2) 即使用户没有共同词，好友请求查询也应该抓取行。 Atm，当询问者用户在其帐户下没有任何文字时，请求消失。

3)按共同单词的数量排序。

SQLfiddle:http://www.sqlfiddle.com/#!2/b158f/1

请注意结果中，word1 word2 是共同的，但welcome 不是共同的。

Answer 1

我不明白你的第二个问题陈述。你能在 SQLfiddle 中给出这个数据的例子吗？否则，请参阅此 http://www.sqlfiddle.com/#!2/13979/17 。在新的 LEFT JOIN 子句中，硬编码的 1 应与 INNER JOIN 子句中的 1 相同。:

SELECT u.name_surname, 
       u.avatar,
       GROUP_CONCAT(DISTINCT w.word ORDER BY w.word ASC) AS asker_words,
       (a.friend_id) AS asker_id
       ,COUNT(w.id) AS WordCount
FROM users u
INNER JOIN 
(
SELECT f1.asker_user_id AS friend_id
    FROM friends AS f1 
    LEFT JOIN friends AS f2
        ON f1.asked_user_id = f2.asker_user_id
        AND f1.asker_user_id = f2.asked_user_id   
    WHERE f1.status = 1 AND f2.status IS NULL
    AND f1.asked_user_id = 1
) a ON a.friend_id = u.id
LEFT JOIN connections c 
  ON u.id = c.user_id
    AND c.invisible <> 1
    AND c.deleted <> 1
LEFT JOIN connections c2
  ON c2.word_id = c.word_id
    AND c2.user_id = 1
    AND c2.invisible <> 1
    AND c2.deleted <> 1
LEFT JOIN words_en w 
  ON w.id = c2.word_id
GROUP BY u.name_surname
ORDER BY WordCount DESC;