我有一个查询如下：
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（如果结果= 1加到SUM，否则不加）结果是同一记录中的值。不进行子查询就可以吗？我不想进行复杂的，未经优化的查询。
这是你想要的吗？
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句