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(t.tahmin = 1)*1) AS ms1,
       (SUM(t.tahmin = 2)*3) AS ms0,
       (SUM(t.tahmin = 3)*1) AS ms2
FROM users u 
LEFT JOIN tahminler t ON u.id = t.user_id 

GROUP BY u.id 
HAVING tahmins_no > 0

我想让 (SUM(t.tahmin = 1)*1) AS ms1 仅在 result = 1 时计算(如果 result = 1 添加到 SUM否则不添加)结果是同一记录中的值。不做子查询就可以吗？我不想有复杂的未优化的查询。

Answer 1

这是你想要的吗？

SELECT u.username, u.id, COUNT(t.tahmin) AS tahmins_no, 
       SUM(t.result = 1) AS winnings,
       SUM(t.result = 2) AS loses,
       (SUM((t.tahmin = 1)*(t.result = 1))*1) AS ms1,
       (SUM((t.tahmin = 2)*(t.result = 1))*3) AS ms0,
       (SUM((t.tahmin = 3)*(t.result = 1))*1) AS ms2
FROM users u LEFT JOIN
     tahminler t
     ON u.id = t.user_id 
GROUP BY u.id 
HAVING tahmins_no > 0;

您还可以将其表达为 case 语句，例如:

sum(case when t.tahmin = 1 and t.result = 1 then 1 else 0 end) * 1 as ms1