您能在MySQL中将一个别名除以另一个别名吗？

Question

我有一个多表查询，类似于此(简化版本)。

SELECT columns, count(table2.rev_id) As rev_count, sum(table2.rev_rating) As sum_rev_rating 
FROM table1
LEFT JOIN table2
ON table1.dom_id = table2.rev_domain_from 
WHERE dom_lastreview != 0 AND rev_status = 1 
GROUP BY dom_url 
ORDER BY sum_rev_rating/rev_count DESC

问题在ORDER BY子句中。这将导致显示一个MySQL错误，如下所示：

不支持

引用“sum_ rev_评级”(引用组函数)

Answer 1

你不能用化名来计算。这样做的一种方法是简单地创建另一个别名并根据别名进行排序。

SELECT columns, count(table2.rev_id) As rev_count, sum(table2.rev_rating) As sum_rev_rating, sum(table2.rev_rating)/count(table2.rev_id) as avg_rev_rating
FROM table1
LEFT JOIN table2
ON table1.dom_id = table2.rev_domain_from 
WHERE dom_lastreview != 0 AND rev_status = 1 
GROUP BY dom_url 
ORDER BY avg_rev_rating DESC

Answer 2

我的mysql生锈了，你可以试试

SELECT columns, count(table2.rev_id) As rev_count, 
    sum(table2.rev_rating) As sum_rev_rating,
    sum(table2.rev_rating)/count(table2.rev_id) as rev_ratio
FROM table1
    LEFT JOIN table2ON table1.dom_id = table2.rev_domain_from 
WHERE dom_lastreview != 0 
AND rev_status = 1 
GROUP BY dom_url 
ORDER BY rev_Ratio DESC

或

SELECT * from (
    SELECT columns, count(table2.rev_id) As rev_count, 
        sum(table2.rev_rating) As sum_rev_rating 
    FROM table1
        LEFT JOIN table2ON table1.dom_id = table2.rev_domain_from 
    WHERE dom_lastreview != 0 
    AND rev_status = 1 
    GROUP BY dom_url 
) X
ORDER BY X.sum_rev_rating/X.rev_count DESC

或

SELECT * from (
    SELECT columns, count(table2.rev_id) As rev_count, 
        sum(table2.rev_rating) As sum_rev_rating,
        sum(table2.rev_rating)/count(table2.rev_id) as rev_ratio
    FROM table1
        LEFT JOIN table2ON table1.dom_id = table2.rev_domain_from 
    WHERE dom_lastreview != 0 
    AND rev_status = 1 
    GROUP BY dom_url 
) X
ORDER BY rev_Ratio DESC