mysql排序和使用子查询之间

Question

我正在执行一个使用连接的查询。因为我使用了一个总金额的子查询。我想按如下子查询的结果排序。

"select users.*, (select sum(amount) as camount from donation where donation.uid=users.id) as camount from users where users.confirmed=1  and camount between 3 and 5 order by camount";

我得到一个错误:1064。

如何使用子查询的结果进行排序查询？

Answer 1

我不知道是什么触发了您遇到的错误，但是将子选择移动到内部联接应该会产生相同的结果。

SQL语句

select  users.*
        , donation.camount
from    users 
        INNER JOIN (
          select  uid
                  , sum( amount ) as camount 
          from    donation 
          group by
                  uid
        ) donation ON donation.uid = users.id
where   users.confirmed = 1  
        and donation.camount between 3 and 5 
order by 
        donation.camount