mysql - MySQL:按用户定义列的绝对值排序

这是我要使用的查询的精简版本：

SELECT user_id,
(SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 AND pref = 1) /
(SELECT COUNT(*) FROM responses WHERE poll_id = 1 AND pref = 1) AS pref_percent,
(SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 ) /
(SELECT COUNT(*) FROM responses WHERE poll_id = 1) AS all_percent,
ABS(all_percent - pref_percent) AS deviation
FROM responses
GROUP BY user_id
ORDER BY deviation DESC

我收到此错误：“字段列表”中的未知列“ all_percent”

显然，在构造另一个用户定义的列（“偏差”）时，无法引用用户定义的列（“ all_percent”）。

因此，我的问题是...如何修改该查询以避免错误？

我真的很想按偏差对查询结果进行排序，而不必在PHP中进行。

最佳答案

在您的当前查询周围包装一个外部查询：

SELECT user_id, pref_percent, all_percent, ABS(all_percent - pref_percent) AS deviation
    FROM (SELECT user_id,
          (SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 AND pref = 1) /
          (SELECT COUNT(*) FROM responses WHERE poll_id = 1 AND pref = 1) AS pref_percent,
          (SELECT COUNT(*) FROM responses WHERE option_id = 1 AND poll_id = 1 ) /
          (SELECT COUNT(*) FROM responses WHERE poll_id = 1) AS all_percent
          FROM responses
          GROUP BY user_id
         ) t
    ORDER BY deviation DESC

关于mysql - MySQL:按用户定义列的绝对值排序，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/5327784/