Mysql 查询使用 SUM 和 Limit 计算使用量和成本
我有一个名为sensor_data 的mysql 数据库。每 30 分钟,一个脚本就会将用电量以及取决于白天/夜间电价的成本插入到该数据库中。
Rec Date Time Reading Usage Cost Tar
2771 2010-12-20 16:00:00 RFXPower 67044.6 0.5 0.09 0.18
我正在寻找的是一个 mysql 查询,它将返回最后一小时的用电量和成本。另一个查询将返回每日使用情况和费用。 对于最后一个查询,它应该简单地将成本列中最后两个值的成本相加。我正在尝试使用总和和限制来执行此操作,但我找不到如何限制最后 2 个当前记录。
I have a mysql database called sensor_data. Every 30 minutes a script inserts the power usage to this database and also the cost depending on the day/night tariff
Rec Date Time Reading Usage Cost Tar
2771 2010-12-20 16:00:00 RFXPower 67044.6 0.5 0.09 0.18
What I am looking for is a mysql query that will return the last hour hour power usage and cost. Another query will return the daily usage and cost.
For the last query, it should simply sum the cost of the last two values in the cost column. I am trying todo this with the sum and limit, but I can't find how to limit the last 2 current records.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
获取最后一小时:
并获取每日总计:
to get the last hour:
and to get the daily total:
由于 SUM 查询仅返回一个聚合行,因此您不能将其与 LIMIT 直接组合。您可以使用子查询:
或者您可以使用日期或时间条件 (
WHERE ...
)。Since the SUM query only returns one aggregated row, you can't combine it with LIMIT directly. You could use a subquery:
Or you could use a date or time condition (
WHERE …
) instead.除非我弄错了,否则你只会遇到第三个查询的问题;前两个查询运行良好。对于第三个查询(总结最后两条记录的成本),您可以按降序对结果集进行排序,并将其限制为两条记录。
Unless I got you wrong, you only have problems with the third query; the first two queries are working well. For the third query (summing up the costs of the last two records), you could sort the result set in descending order and limit it to two records.