mysql - MySQL在排名中包含所有结果

Question

我正在使用以下查询进行排序，Order by用于 4 个不同的排名：id、avg_rating、total_sent & points

但是，我无法将所有成员都包含在结果中。我想包括所有成员，包括那些有 0 的成员：total_sent、total_received、points、avg_rating、votes

请帮助我了解我所缺少的。谢谢你。

  SELECT m.id, 
         m.Name,
         m.City,
         m.Zip_Code,
         m.url, 
         r.avg_rating, 
         r.votes,
         froms.from_ct total_sent,
         tos.to_ct     total_received,
         froms.from_ct - tos.to_ct `points`
  FROM members m
  JOIN (
    SELECT id_rated, 
           avg(rating) avg_rating,
           count(*) votes
    FROM member_ratings
    GROUP BY id_rated
  ) r ON r.id_rated = m.id
  JOIN ( SELECT id_from, COUNT(*) AS from_ct FROM member_points GROUP BY 1
       ) AS froms  ON froms.id_from = m.id
  JOIN ( SELECT id_received,   COUNT(*) AS   to_ct FROM member_points GROUP BY 1
       ) AS tos    ON tos.id_received = m.id
  WHERE m.Account_Active = 'TRUE'
  GROUP BY m.id, 
           m.Name
  ORDER BY `avg_rating` DESC;

标签： mysqljoingroup-bysql-order-by

Answer 1

对所有表使用 LEFT JOIN

   SELECT m.id, 
         m.Name,
         m.City,
         m.Zip_Code,
         m.url, 
         r.avg_rating, 
         r.votes,
         froms.from_ct total_sent,
         tos.to_ct     total_received,
         froms.from_ct - tos.to_ct `points`
  FROM members m
  LEFT JOIN (
    SELECT id_rated, 
           avg(rating) avg_rating,
           count(*) votes
    FROM member_ratings
    GROUP BY id_rated
  ) r ON r.id_rated = m.id
  LEFT JOIN ( SELECT id_from, COUNT(*) AS from_ct FROM member_points GROUP BY 1
       ) AS froms  ON froms.id_from = m.id
  LEFT JOIN ( SELECT id_received,   COUNT(*) AS   to_ct FROM member_points GROUP BY 1
       ) AS tos    ON tos.id_received = m.id
  LEFT JOIN member_points mp ON mp.id_points = m.id
  WHERE m.Account_Active = 'TRUE'
  GROUP BY m.id, 
           m.Name
  ORDER BY `avg_rating` DESC;