mysql - 如何按 SELECT 查询中两个 SUM/COUNT 的比率进行排序？

Question

在SELECT查询中

SELECT 
category_id, 
COUNT(*) AS Total, 
SUM(IF(year=2016,1,0) AS year2016, 
SUM(IF(year=2015,1,0) AS year2015
FROM articles
GROUP BY category_id

如何ORDER BYyear2016/year2015的比率？

Answer 1

将 SELECT 语句移动到子查询中将允许您重新使用计算的 SUM 聚合。转换为 DECIMAL 将告诉 MySQL 不要使用整数除法并将值保留在小数点右侧。

SELECT category_id, Total, year2016, year2015
FROM
  (SELECT 
    category_id, 
    COUNT(*) AS Total, 
    SUM(IF(year=2016,1,0)) AS year2016, 
    SUM(IF(year=2015,1,0)) AS year2015
  FROM articles
  GROUP BY category_id) AS t
ORDER BY CAST(year2016 AS DECIMAL(14,4)) / CAST(year2015 AS DECIMAL(14,4))