我有一条这样的声明,其中我需要将多个别名结合为另一个总和并获得输出。
select
t1.id,
sum(t2.answ = t1.answ) as answerA,
sum(t3.answ = t1.answ) as answerM,
sum(t4.answ = t1.answ) as answerD,
sum(answerA + answerM + answerD) as total
from t1
left join t2 on t2.id = t1.t2fk
left join t3 on t3.id = t1.t3fk
left join t4 on t4.id = t1.t4fk
group by t1.id // updated
这可能吗?
sum(answerA + answerM + answerD) as total
不,您不能在同一查询中引用列别名。您将不得不使其成为子查询。
SELECT id, answerA, answerB, answerC, (answerA + answerM + answerD) AS total
FROM (
select
t1.id,
sum(t2.answ = t1.answ) as answerA,
sum(t3.answ = t1.answ) as answerM,
sum(t4.answ = t1.answ) as answerD,
sum(answerA + answerM + answerD) as total
from t1
left join t2 on t2.id = t1.t2fk
left join t3 on t3.id = t1.t3fk
left join t4 on t4.id = t1.t4fk
GROUP BY t1.id
) AS subquery
或者您可以编写一个等效的表达式。
select
t1.id,
sum(t2.answ = t1.answ) as answerA,
sum(t3.answ = t1.answ) as answerM,
sum(t4.answ = t1.answ) as answerD,
sum(t1.answ IN (t2.answ, t3.answ, t4.answ)) as total
from t1
left join t2 on t2.id = t1.t2fk
left join t3 on t3.id = t1.t3fk
left join t4 on t4.id = t1.t4fk
GROUP BY t1.id
选项2更清洁且有效。非常感谢Barmar。