时间戳

首页 > 解决方案 > 在同一查询中重用计算字段

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

问题描述

我有这个查询。

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不是一个已知字段（它是一个派生字段）。第一个查询有效，但效率不高。第二个工作我缺少什么？

标签： mysqlsqlcalculated-field

解决方案

您可以使用公用表表达式 (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

推荐阅读