mysql - 如何在同一个查询中添加多个聚合函数的结果？

Question

我有一个具有数学计算和聚合函数的查询，如下所示:

SELECT u.username, u.id, COUNT(t.tahmin) AS tahmins_no, 
       SUM(t.result = 1) AS winnings,
       SUM(t.result = 2) AS loses,
       sum(case when t.tahmin = 1 and t.result = 1 then 1 else 0 end) * 1 as ms1,
       sum(case when t.tahmin = 2 and t.result = 1 then 1 else 0 end) * 3 as ms0,
       sum(case when t.tahmin = 3 and t.result = 1 then 1 else 0 end) * 1 as ms2,
       sum(case when t.tahmin = 4 and t.result = 1 then 1 else 0 end) * 2 as alt,
       sum(case when t.tahmin = 5 and t.result = 1 then 1 else 0 end) * 2 as ust,
       sum(case when t.tahmin = 6 and t.result = 1 then 1 else 0 end) * 3 as tg_0_1,
       sum(case when t.tahmin = 7 and t.result = 1 then 1 else 0 end) * 2 as tg_2_3,
       sum(case when t.tahmin = 8 and t.result = 1 then 1 else 0 end) * 4 as tg_4_6,
       sum(case when t.tahmin = 9 and t.result = 1 then 1 else 0 end) * 20 as tg_7,
       sum(case when t.tahmin = 10 and t.result = 1 then 1 else 0 end) * 1 as kg_var,
       sum(case when t.tahmin = 11 and t.result = 1 then 1 else 0 end) * 1 as kg_yok
       sum(ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok) as total    
FROM users u 
LEFT JOIN tahminler t ON u.id = t.user_id 
LEFT JOIN matches_of_comments mc ON t.match_id = mc.match_id
WHERE MONTH(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) = 01 AND 
      YEAR(STR_TO_DATE(mc.match_date, '%d.%m.%Y')) =  2014 AND flag=1
GROUP BY u.id 
HAVING tahmins_no > 0
ORDER BY total DESC

查询工作得很好，我得到了预期的结果，唯一的问题是当我将以下行添加到查询中时:

sum(ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok) 总计

我想按总计对我的列进行排序我想知道该行是否正确？它的语法正确与否？

Answer 1

您正在尝试获取 SUM 的 SUM(在这种情况下需要 noz)并且想要重用别名，这两种情况在标准 SQL 中都是不允许的(没有派生表)。

如果只是为了排序，你只需要

ORDER BY ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok DESC

如果您想在 SELECT 列表中显示 TOTAL，您需要重复计算:

   sum(case when t.tahmin = 1 and t.result = 1 then 1 else 0 end) * 1 +
   sum(case when t.tahmin = 2 and t.result = 1 then 1 else 0 end) * 3 +
   ....
   sum(case when t.tahmin = 11 and t.result = 1 then 1 else 0 end) * 1 as TOTAL

或者更好地使用派生表:

SELECT username, id, tahmins_no, 
       winnings,
       loses,
       ms1,
       ms0,
       ...
       kg_yok,
       ms1 + ms0 + ms2 + alt + ust + tg_0_1 + tg_2_3 + tg_4_6 + tg_7 + kg_var + kg_yok as TOTAL
FROM 
  (
    your existing query (without ORDER BY)
  ) as dt
ORDER BY TOTAL DESC