我有一张客户表,那里的用电量每天晚上都会更新。我想计算一下每月的用电量。
SELECT customer, max(power_usage)-min(power_usage) AS lastmonthpower_usage FROM usage
WHERE YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
GOUP by customer
似乎是首先对数据库中每个客户的所有记录进行计算,然后对where子句进行筛选。我想先筛选日期,然后计算用电量。
您的查询看起来很好(在GROUP BY
上没有拼写错误,也没有转义MySQL的关键字/保留字(,应该会给出预期的结果。
但是您也可以在GROUP BY
上使用月份和年份。现在,您应该获得正确的年和月的最小值和最大值,以便在没有WHERE
的情况下进行计算。相反,您可以添加HAVING
来过滤最后一个月(或任何其他月份(的分组结果:
SELECT YEAR(`date`) AS dYear, MONTH(`date`) AS dMonth, customer, MAX(power_usage) - MIN(power_usage) AS lastmonthpower_usage
FROM `usage`
GROUP BY customer, YEAR(`date`), MONTH(`date`)
HAVING dYear = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND dMonth = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
dbfiddle.uk上的演示
还要确保避免/不要使用MySQL的关键字或保留字,如date
或usage
。如果你想在查询中使用关键字或保留字,你必须用反勾号(`
-SELECT * FROM `usage`
(来转义它们。
您可以使用子查询
SELECT customer, max(power_usage)-min(power_usage) AS lastmonthpower_usage
from
(select * from usage
WHERE YEAR(date) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
)A
GOUP by customer