mysql - 在同一查询中重用计算字段

Question

我有这个问题。

SELECT carte.nome, sum(amount) AS total, (1500-sum(amount)) AS residuo 
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id 
WHERE data BETWEEN '2019-05-01' AND '2019-05-31' 
GROUP by banca

这个查询的目标是在一段时间内求和一些金额(sum(amount))。我还将此金额的下限设置为 1500，因此我想在第三个字段中进行计算。我试过了

SELECT carte.nome, sum(amount) AS total, (1500-total) AS residuo 
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id 
WHERE data BETWEEN '2019-05-01' AND '2019-05-31' 
GROUP by banca

但 mysql 提示 totale 不是已知字段(它是派生字段)。第一个查询有效，但效率不高。我缺少什么才能让第二个工作？

Answer 1

您可以使用公用表表达式 (CTE):

WITH cte_query AS (
SELECT carte.nome AS nome, sum(amount) AS total
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id 
WHERE data BETWEEN '2019-05-01' AND '2019-05-31' 
GROUP by banca
)

SELECT nome, total, (1500- total) AS residuo 
FROM cte_query

子查询也可以:

SELECT nome, total, (1500- total) AS residuo 
FROM (
SELECT carte.nome AS nome, sum(amount) AS total
FROM movimenti_carta JOIN carte ON movimenti_carta.banca=carte.id 
WHERE data BETWEEN '2019-05-01' AND '2019-05-31' 
GROUP by banca
) A