通过减去车辆读数来计算车辆的里程 [英] Calculate Mileage of Vehicles by Subtracting from Vehicle Readings

问题描述

我有一张表，其中定期存储车辆行驶里程，即车辆行驶里程.

I have a table in which vehicle mileage i.e the mileage of the vehicle is stored on a regular basis.

The table:
----------
ID (primary key)
Vehicle_id 
date_of_reading (datetime)
Mileage

到月底，我想选择上个月的最后一个读数，并从本月的最后一个读数中减去它，以获得所有车辆行驶的总公里数.

By the end of the month i want to pick the Last reading of the Past Month and subtract it from the Last Reading of this Month to get the Total Km that all the vehicles have traveled.

http://sqlfiddle.com/#!9/34b8b

表的结构

CREATE TABLE `tbl_readings` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `vehicle_id` int(11) NOT NULL,
 `km_driven` int(11) NOT NULL,
 `dt_of_reading` datetime NOT NULL,
  `dt_of_entry` datetime NOT NULL,
  `entry_user` varchar(50) NOT NULL,   PRIMARY KEY (`id`),
  KEY `vehicle_id` (`vehicle_id`),  CONSTRAINT `tbl_readings_ibfk_1`     FOREIGN KEY (`vehicle_id`) REFERENCES `table_vehicle_info` (`id`) ON DELETE     CASCADE ON UPDATE CASCADE) ENGINE=InnoDB AUTO_INCREMENT=394     DEFAULT CHARSET=latin1

该表几乎每天都会发布车辆仪表读数.到月底，这些读数将用于计算车辆行驶的公里数. Km的编号必须通过获取上个月的最后一个读数，然后从当前月份的最后一个读数中减去来得出.

Almost every day vehicle Meter readings are posted in this table. And by the end of the month these readings are used to calculate the number of KM the vehicles are driven. The no of Km have to be calculated by taking the Last Reading of the previous month and subtracting it from the last reading of the current Month.

我希望现在更加清楚.我意识到我的问题太模棱两可了.

I hope it is much clearer now. I realize my question was way too ambiguous.

推荐答案

我有同样的问题.看来我用以下代码解决了这个问题:

I had the same question. Looks like I solved it with the following code:

SELECT Main.vehicle_id, Main.latestreading as currentmileage, Link.latestReadin as previousmilage 
FROM (SELECT m1.*, max(l1.rowid) as LinkID
FROM `foo` as m1
JOIN `foo` as l1
ON l1.vehicle_id= m1.vehicle_id
WHERE l1.date< m1.date
GROUP BY m1.rowid
ORDER BY m1.rowid desc) as Main
LEFT JOIN (SELECT * FROM `foo`) as Link
ON Link.rowid = Main.LinkID

这篇关于通过减去车辆读数来计算车辆的里程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！