Mysql - 按另一个语句的计数排序

Question

我正在尝试根据 friend 的未读消息数对当前用户的 friend 列表进行排序。好友对当前用户的未读消息越多，他应该被放置在列表中的位置就越高。

我设法组合了一个查询，该查询返回特定用户 ID 中当前用户的未读消息计数。

SELECT COUNT(*) AS unread_msg
FROM messages m LEFT JOIN users u ON m.from_user_id = u.id
    WHERE 1 /* Current user, unread msg to */ IN (from_user_id,to_user_id)
      AND 2 /* Friend, unread msg from */ IN (from_user_id,to_user_id)
      AND to_user_id = 1 /* Current user, unread msg to */
      AND seen = 0 

在 SO-sql 专家的帮助下，我查询了好友列表:

SELECT a.name_surname,
       a.avatar,
       GROUP_CONCAT(DISTINCT w.word ORDER BY w.word ASC) AS friend_words,
       (a.friend_id) AS friend_msg_id /* Unread msg from id */
FROM (
  SELECT f1.asked_user_id AS friend_id,
       f1.created,
       u.name_surname,
       u.avatar
  FROM friends AS f1 
  INNER JOIN friends AS f2 ON f1.asked_user_id = f2.asker_user_id
  INNER JOIN users AS u ON f1.asked_user_id = u.id
       AND f1.asker_user_id = f2.asked_user_id
  WHERE f1.status = 1 AND f2.status = 1
       AND f1.asker_user_id = 1 /* Current user id */
) a
LEFT JOIN connections c ON c.user_id = a.friend_id 
LEFT JOIN words_en w ON c.word_id = w.id
GROUP BY 1

有什么想法可以统一这些查询吗？所以我在第二个查询中得到了 unread_msg ？

和 fiddle :http://www.sqlfiddle.com/#!2/129a6/1

Answer 1

这能满足您的需要吗？

我刚刚获取了您的第二个查询，将其嵌套并添加了与消息表和适当分组的左联接。

我相信它可以更有效地构建(无需额外的嵌套)，但我有点担心它如何与你在那里的 GROUP_CONCAT 一起执行(加上我不确定单词表的作用)，并且那里您的表中没有足够的数据来测试它。

select b.name_surname,b.avatar,b.friend_words,b.friend_msg_id, count(m.id) from (
    SELECT a.name_surname as name_surname,
           a.avatar as avatar,
           GROUP_CONCAT(DISTINCT w.word ORDER BY w.word ASC) AS friend_words,
           (a.friend_id) AS friend_msg_id /* Unread msg from id */
    FROM (
      SELECT f1.asked_user_id AS friend_id,
           f1.created,
           u.name_surname,
           u.avatar
      FROM friends AS f1 
      INNER JOIN friends AS f2 ON f1.asked_user_id = f2.asker_user_id
      INNER JOIN users AS u ON f1.asked_user_id = u.id
           AND f1.asker_user_id = f2.asked_user_id
      WHERE f1.status = 1 AND f2.status = 1
           AND f1.asker_user_id = 1 /* Current user id */
    ) a
    LEFT JOIN connections c ON c.user_id = a.friend_id 
    LEFT JOIN words_en w ON c.word_id = w.id
) b
left join messages m on m.to_user_id = 1 
          and m.from_user_id = b.friend_msg_id 
          and m.seen = 0
group by b.name_surname,b.avatar,b.friend_words,b.friend_msg_id