推荐好友和共同好友sql

表结构：

sql语句：

SELECT max(tuijian_info.nick_name) nickName,
               max(tuijian_info.icon) icon,
               USER_GROUP.USER_ID userId,
               COUNT(1) commonNumber,
               max(USER_GROUP_FRIEND.FRIEND_ID) commonUserId,
               max(gongtong_info.nick_name) commonNickName
          FROM USER_GROUP_FRIEND
          LEFT JOIN USER_GROUP ON USER_GROUP.USER_GROUP_ID =
                                  USER_GROUP_FRIEND.USER_GROUP_ID
          left join user_info tuijian_info on tuijian_info.user_id =
                                              USER_GROUP.USER_ID
          left join user_info gongtong_info on gongtong_info.user_id =
                                               USER_GROUP_FRIEND.FRIEND_ID
         WHERE USER_GROUP_FRIEND.FRIEND_ID IN
               (SELECT USER_GROUP_FRIEND.FRIEND_ID
                  FROM USER_GROUP_FRIEND
                  LEFT JOIN USER_GROUP ON USER_GROUP.USER_GROUP_ID =
                                          USER_GROUP_FRIEND.USER_GROUP_ID
                 WHERE USER_GROUP.USER_ID = #{userId} and USER_GROUP_FRIEND.DELETE_FLAG=0)
           and USER_GROUP.USER_ID != #{userId}
           and USER_GROUP_FRIEND.DELETE_FLAG=0
         GROUP BY USER_GROUP.USER_ID
         order by commonNumber desc

结果：