MySQL:如何使用别名构造新字段？

Question

我正在尝试使用两个别名(like_count 和 dislike_count)来构造一个用于订单的新字段(score)目的。下面是我当前的查询，但它不起作用。我在以下查询中收到以下错误:“‘字段列表’中存在未知列‘like_count’”。我在 SO 上寻找过类似的问题，但未能找到解决方案。有人知道如何做吗？

SELECT comment.id, comment.content, 

SUM(if(comment_reaction.type = 'like', 1, 0)) as like_count,
SUM(if(comment_reaction.type = 'dislike', 1, 0)) as dislike_count,

((like_count + 1.9208) / (like_count + dislike_count) - 
    1.96 * SQRT((like_count * dislike_count) / (like_count + dislike_count) + 0.9604) / 
        (like_count + dislike_count)) / (1 + 3.8416 / (like_count + dislike_count))
as score,

(SELECT comment_reaction.type FROM comment_reaction
WHERE comment_reaction.person_id = :person_id
AND comment.id = comment_reaction.comment_id) as my_reaction

FROM comment
LEFT JOIN comment_reaction ON comment.id = comment_reaction.comment_id

WHERE comment.topic_id = :topic_id  
GROUP BY comment.id
ORDER BY score DESC LIMIT 0, 10

Answer 1

将原始查询包装在派生表中。然后添加新列:

select dt.*,
    ((like_count + 1.9208) / (like_count + dislike_count) - 
        1.96 * SQRT((like_count * dislike_count) / (like_count + dislike_count) + 0.9604) / 
            (like_count + dislike_count)) / (1 + 3.8416 / (like_count + dislike_count))
    as score

from
(
SELECT 
    comment.id, comment.content, 

    SUM(if(comment_reaction.type = 'like', 1, 0)) as like_count,
    SUM(if(comment_reaction.type = 'dislike', 1, 0)) as dislike_count,

    (SELECT comment_reaction.type FROM comment_reaction
    WHERE comment_reaction.person_id = :person_id
    AND comment.id = comment_reaction.comment_id) as my_reaction,

    FROM comment
    LEFT JOIN comment_reaction ON comment.id = comment_reaction.comment_id

    WHERE comment.topic_id = :topic_id

    GROUP BY comment.id
) dt
ORDER BY score DESC