mysql - 无法从子查询结果中选择计算值

Question

我的目标是从另一个表中获取一些计数，然后计算差异并按该差异进行排序，以确定表现最好和最差的结果。

select
    `id`,
    `subscriber_count`,
    ([SUBQUERY]) as `subscribed_count`,
    ([SUBQUERY]) as `unsubscribed_count`,
    (`subscribed_count` - `unsubscribed_count`) AS `difference`
from `mytable`
order by `difference` ASC

问题是我收到此错误:

Error Code: 1054. Unknown column 'subscribed_count' in 'field list'

如何按差价订购并选择它？

Answer 1

请尝试此代码

select
    `id`,
    `subscriber_count`,
    ([SUBQUERY]) as `subscribed_count`,
    ([SUBQUERY]) as `unsubscribed_count`,
    (([SUBQUERY]) - ([SUBQUERY])) AS `difference`
from `mytable`

问题:subscribed_count、unsubscribed_count 不是字段，它们只是 mysql 中的别名，因此无法从别名进行任何操作。

谢谢。希望它有帮助:)