不支持引用'r1‘(引用组函数)

Question

有人能帮忙吗？我想按总评等定购用户DESC，两个评等都在不同的表中，我的sql：

SELECT dle_users.user_id, dle_users.user_group, dle_users.name, 
       dle_users.foto, 
       SUM(dle_comments.rating) as r1, 
       SUM(dle_post_extras.rating) as r2
FROM dle_users
JOIN dle_comments
ON dle_comments.user_id = dle_users.user_id
JOIN dle_post_extras ON dle_post_extras.user_id = dle_users.user_id
WHERE dle_comments.rating 
AND dle_post_extras.rating > 0
GROUP BY dle_users.user_id
ORDER BY SUM(r1+r2) DESC 
LIMIT 0,10

Answer 1

加入前的合计，否则你的金额将被取消：

SELECT u.user_id, u.user_group, u.name, u.foto, 
       COALESCE(c.r1, 0) as r1, COALESCE(e.r2, 0) as r2 
FROM dle_users u LEFT JOIN
     (SELECT user_id, SUM(rating) as r1
      FROM dle_comments
      GROUP BY user_id
     ) c
     ON c.user_id = u.user_id LEFT JOIN
     (SELECT user_id, SUM(rating) as r2
      FROM dle_post_extras
      WHERE rating > 0
      GROUP BY user_id
     ) e
     ON e.user_id = u.user_id
GROUP BY dle_users.user_id
ORDER BY COALESCE(r1, 0) + COALESCE(r2, 0) DESC 
LIMIT 0, 10