将别名与其他 MySQL 列值相加

Question

我对此查询有一个问题;我似乎无法((total + rec_host) / 2) AS total2工作。我将如何在不执行以下操作的情况下执行此过程：

((((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) + rec_host ) / 2)

这是我的查询：

   SELECT host_name, 
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) AS total,
       ((total + rec_host) / 2) AS total2
   FROM lhr_reviews 
   GROUP BY host_name
   ORDER BY total 
   DESC LIMIT 0,10

Answer 1

使用如下所示

的子查询：

SELECT 
  host_name, 
  cnt1, 
  cnt2,
  cnt3,
  cnt4,
  cnt5,
  cnt6,
  rh1,
  rh2,
  total,
  ((total + rec_host) / 2) AS total2
FROM
(
   SELECT host_name, 
       rec_host,
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + 
         rank_csb + rank_vfm + rank_orr
         ) / 6) AS total
   FROM lhr_reviews 
   GROUP BY host_name, rec_host
) t 
ORDER BY total 
DESC LIMIT 0,10;

Answer 2

你可以做的是：

select x.*, ((x.total + rec_host) / 2) AS total2
from (
SELECT host_name, rec_host,
       SUM(rank_ur) AS cnt1, 
       SUM(rank_scs) AS cnt2,
       SUM(rank_tsk) AS cnt3,
       SUM(rank_csb) AS cnt4,
       SUM(rank_vfm) AS cnt5,
       SUM(rank_orr) AS cnt6,
       SUM(IF(rec_host = 1,1,0)) AS rh1,
       SUM(IF(rec_host = 0,1,0)) AS rh2,
       ((rank_ur + rank_scs + rank_tsk + rank_csb + rank_vfm + rank_orr) / 6) AS total
   FROM lhr_reviews 
   GROUP BY host_name
   ORDER BY total 
   DESC LIMIT 0,10
) as x
;

当别名和其他列处于同一SELECT级别时，不能将该列用作别名。因此，您可以使用派生查询，该查询基本上可以重命名列和/或命名任何计算列。在这里查看鲁本斯·法里亚斯和罗伯·范达姆的答案

PS：会搜索更好的文章来更新答案:)